1

I have a PHP array of data (facebook user ids) which I want to compare against the ones stored in my database (which will consist of thousands). Of those found in the database, I want them to be separated into a new array. I have no idea how to go about this.

So I begin with this array
$ids = 3312312,1232424,1242234,2342636,457456,345345

and end with two
$found = 34234234,234234234
$notfound = 23234234,234234,23423423

If anyone could help, that would be great. I've started this a few different ways but not got very far. Ideally I'd like this comparison to be done in once but I'm not sure if that's possible.

Thanks!

EDIT

From what you've said, I've come up with the following code quickly. It seems to be what you are getting it, but I've not been able to slowly build up to this point so I'm not sure if it's right. 

<?php
$con=mysqli_connect("localhost","root","","sabotage");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

//json array is being posted to this file from another page
$jsonarray = '[{"name":"Lizzie OBrien","id":"218101335"},{"name":"Ellis Ward","id":"512376340"}]';
$friendlist = json_decode($jsonarray, true);

$found = [];
$notfound = [];

foreach($friendlist as $friend){

    $friendid = $friend['id'];
    $checkUserID = mysql_query("SELECT facebookid from users WHERE facebookid = '$friendid'");

    if (!$checkUserID) {
        die('Query failed to execute for some reason');
    }

    if (mysql_num_rows($checkUserId) > 0) {
        $found[] = $id;
    }else{
        $notfound[] = $id;
    }

}


mysqli_close($con);

?>

Which gives me:

Query failed to execute for some reason

Does it make a difference that my facebookid column is an Integer?

Thanks

themartin
  • 87
  • 11

3 Answers3

1

How I would do it:

$idsfromdb; //grab all ids from the db, and put in array
$idstobetested; //array of all ids you want to compare
$found = [];
$notfound[];

foreach($idstobetested as $id){
  if(in_array($id, $idsfromdb)){
    $found[] = $id;
  }else{
    $notfound[] = $id;
  }
}

However:

After seeing your comment, if your db has a large number of records, instead of selecting them all and putting it into an array. Instead, iterate through your array of ids that you want to test and run a select query on the db, if that does not return false, the value exists in the db and you can then push the id to the found array.

This may be of use: How to check if value already exists in MySQL database

Community
  • 1
  • 1
1321941
  • 2,139
  • 5
  • 26
  • 49
  • Thanks for that. I've actually been failing to get that code to work already, from the link you provided. Getting the "Query failed to execute for some reason" error, although I'm sure my row and table names are correct. This is why I thought stuff it, i'll start again and ask SO :P – themartin Sep 21 '13 at 11:03
  • Perhaps because I seem to be mixing mysqli queries with mysql queries. Will keep investigating. – themartin Sep 21 '13 at 11:08
  • @themartin if you update your question with your code, I can take a look if you like? – 1321941 Sep 21 '13 at 11:10
  • I'll try. There are a few parts to this problem, and I've not yet got any of them working individually so it might not make any sense. Give me a minute. – themartin Sep 21 '13 at 11:13
0

You might be looking for this functions.

$jsonarray = '[{"name":"Lizzie OBrien","id":"218101335"},{"name":"Ellis Ward","id":"512376340"}]';
$friendlist = json_decode($jsonarray, true);

$friendIds = array_map( create_function('$data', 'return $data["id"];'), $friendlist);

// Will return all the matched records
$sql1 = "SELECT yourcolumnname FROM yourtablename WHERE facebookid IN (".implode(',', $friendIds).")";

// Will return all the unmatched records
$sql2 = "SELECT yourcolumnname FROM yourtablename WHERE facebookid NOT IN (".implode(',', $friendIds).")";
Ravi Kant Mishra
  • 778
  • 1
  • 7
  • 13
  • Thanks for this, but as one array (from the database) will be in the thousands, I can't do it this way. – themartin Sep 21 '13 at 12:00
  • As per all possible solutions you will have to fetch all the facebook ids from database. and instead executing queries for all ids you can use `in` query. – Ravi Kant Mishra Sep 21 '13 at 12:05
  • I know I'm new to this, but I don't think I *have* to fetch all IDs from the database. This will be a lot of data. As per my latest edit, I think I need to check the database first, then act on it. – themartin Sep 21 '13 at 12:14
  • Thanks for that, seems promising. I've added this to the end of your provided code `$result = mysql_query($sql1); if (!$result) { // add this check. die('Invalid query: ' . mysql_error()); } $row = mysql_fetch_array($result); echo $row['facebookid'];` But end up with Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 – themartin Sep 21 '13 at 16:40
0

This is the code that did it for me. Couldn't have done it without your help.

<?php
$con=mysql_connect("localhost","root","");
// Check connection
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

//json array is being posted to this file from another page
$jsonarray = '[{"name":"Lizzie OBrien","id":"218101335"},{"name":"Ellis Ward","id":"512376340"}]';
$friendlist = json_decode($jsonarray, true);


$found = [];
$notfound = [];

foreach($friendlist as $friend){

    $friendid = $friend['id'];
    mysql_select_db("sabotage", $con);
    $result = mysql_query("SELECT facebookid FROM users WHERE facebookid ='$friendid'", $con);

    if (mysql_num_rows($result) > 0) {
        $found[] = $friendid;
    }else{
        $notfound[] = $friendid;
    }

}

mysql_close($con);

?>
themartin
  • 87
  • 11
  • no doubt your answer will work. But you are executing your query in loop that means you will have to run 1000 times your query if you have 1000 records. Thats not the optimized way. – Ravi Kant Mishra Sep 22 '13 at 03:56