mysql and php problems to return array.

Question

I am having a problem returning a JSON array after trying to do a query where I want the query to return the first name, last name , and email after giving it numerous ids. How would I go about returning an array with rows including the above parameters after giving it ids. here is what I have

This works :

$qry = "SELECT ALL $mysql_database.$patientsTable.Users_idUser FROM $mysql_database.$patientsTable WHERE doctorsTable_id_doctorsTable=$qr";
            $res = mysql_query($qry,$connect) or die(mysql_error());

then this is what I am working on where it is not working :

      $arr_length = count($arr);            
       for($i=1;$i<=$arr_length;$i++)
        {

            $integerIDs = json_decode('[' .json_encode($arr[$i]['Users_idUser']) . ']', true);

            $q = "SELECT firstName,lastName,email from $mysql_database.$UsersTable WHERE idUser='$integerIDs[$i]'";
            $res1 = mysql_query($q,$connect) or die(mysql_error());


        }

I want to for loop to return the above question but i am having problem with this.

Please show your original array. Why you encode/decode it? Also, you need to show desire final JSON. — fusion3k, Mar 30 '16 at 23:54
[Don't use `mysql_*`!](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php?rq=1) Use `mysqli_*` or `PDO`. Also, why are your database name and your table name in variables? — Darwin von Corax, Mar 31 '16 at 00:00
@fusion3k that encode and decode returns the ids as ints of the users id from the previous queries which returns strings. — Aboogie, Mar 31 '16 at 00:08
Why don't you simply use `INNER JOIN`? It will reduce necessary of multiple querying to the database and improve performance of your application. — max, Mar 31 '16 at 00:08
Take a look at [`INNER JOIN` syntax](http://www.mysqltutorial.org/mysql-inner-join.aspx) and - I repeat - take time to revise your question adding more details (tables sample, desired result), otherwise you will waste a lot of time in comments chat. — fusion3k, Mar 31 '16 at 00:14

score 0 · Answer 1 · answered Mar 31 '16 at 00:16

You may do this with one query using JOIN expression as follow:

SELECT firstName,
    lastName,
    email 
FROM $mysql_database.$UsersTable u
    INNER JOIN $mysql_database.$patientsTable p ON p.Users_idUser = u.idUser
WHERE p.doctorsTable_id_doctorsTable=$qr

However, I would suggest you using PDO abstraction over mysql_* functions and PHP variables as parameters to communicate with database as more reliable and comprehensive approach.

mysql and php problems to return array.

1 Answers1