How to separate a php array based on sql results?

Question

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

But I think from that I'd need the entire array of SQL values? It's likely that I will be comparing my array of about 500 with tens of thousands of sql rows. — themartin
– themartin, Commented Sep 21, 2013 at 10:42
I'm not sure. You might be able to tell I'm not a pro at this stuff. My table has 3 columns, id(primary key, auto increment, integer), name(varchar), and facebookid(int). It's the facebookid that I'm looking at. Cheers — themartin
– themartin, Commented Sep 21, 2013 at 11:11

Community · Accepted Answer · 2017-05-23 11:56:35Z

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

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Sep 21, 2013 at 10:44

1321941

2,1766 gold badges27 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

themartin Over a year ago

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 Over a year ago

Perhaps because I seem to be mixing mysqli queries with mysql queries. Will keep investigating.

1321941 Over a year ago

@themartin if you update your question with your code, I can take a look if you like?

themartin Over a year ago

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 · Accepted Answer · 2013-09-21 12:54:58Z

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);

?>

answered Sep 21, 2013 at 12:54

themartin

8511 bronze badges

1 Comment

Ravi Kant Mishra Over a year ago

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 · Accepted Answer · 2013-09-22 03:57:18Z

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).")";

edited Sep 22, 2013 at 3:57

answered Sep 21, 2013 at 11:04

Ravi Kant Mishra

7681 gold badge7 silver badges14 bronze badges

4 Comments

themartin Over a year ago

Thanks for this, but as one array (from the database) will be in the thousands, I can't do it this way.

Ravi Kant Mishra Over a year ago

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.

themartin Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

How to separate a php array based on sql results?

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related