Using array in php query?

Question

i want to obtain number of rows in query using mysql_num_rows.the query itself contains 2 arrays. i want to store the number in array. Here's my code

  $antecedent=array();

for($i=0;$i<=$index;$i++){
    if(isset($period[$i])|| isset($gpa[$i]) || isset($antecedent[$i])){
  $queryAntecedent=mysql_query("SELECT * FROM mytable WHERE  study_period='$period[$i]' AND ipk='$gpa[$i]'") or die (mysql_error());    

        $antecedent[$i]=mysql_num_rows($queryAntecedent);
        }//endif
}//endfor

print_r ($antecedent);

when i only used 1 array inside query, the code works. on the other hand, when i put 2 arrays in it..the code doesn't work(all element in array antecedent contain 0). How to slve this? is it no way using 2 arrays in query? Thank you

It might be that `$period[$i]` and `$gpa[$i]` are never set for the same `$i`. Then you will create a query which does not return anything... — Felix Kling, Jul 14 '11 at 15:07
so, how can i fix it? both have the same number of element derived from the previous process. — aTFa, Jul 14 '11 at 15:10
If you want count you are doing it wrong. You may want to use `empty` instead of `isset`... — Dejan Marjanović, Jul 14 '11 at 15:10

score 3 · Answer 1 · answered Jul 14 '11 at 15:08

3

use { } in the interpolation or concat:

This:

"SELECT * FROM mytable WHERE  study_period='{$period[$i]}' AND ipk='{$gpa[$i]}'"

Or This:

"SELECT * FROM mytable WHERE  study_period='" . $period[$i] . "' AND ipk='" . $gpa[$i]} . "'"

An even better solution is to use PDO or something similar which will allow you to use placeholders and prepared queries. Placeholders help prevent SQL Injection which this query could be vulnerable to. And prepared queries will give you much better performance especially in a loop like you have here.

answered Jul 14 '11 at 15:08

Cfreak

19,191
6
49
60

If you aren't using PDO you should sanitize those variables to protect against SQL injection. – Localghost Jul 14 '11 at 15:11
i've already tried using bracket and the second way. but, i still couldn't get any value in $antecedent array. maybe i can't put 2 arrays in query :( – aTFa Jul 15 '11 at 01:50
If the count is 0 it's likely that's what your query is returning. – Cfreak Jul 15 '11 at 03:31
`{}` notation isn't necessary for interpolating a 1-dimensional array reference in a string. You'd need them it was multidimensonal, though. `"$arr[1][2]"` would be seen as `{$arr[1]}[2]` by PHP unless you used the `{}` notation. – Marc B Jul 15 '11 at 14:08

score 1 · Accepted Answer · answered Jul 14 '11 at 15:15

1

If you're not getting a mysql error when the query runs, then your query is simply returning no rows. Try building the query separately and store it in a variable, so you can view what you've built:

$sql = "SELECT * FROM mytable WHERE  study_period='$period[$i]' AND ipk='$gpa[$i]'";
$queryAntecedent=mysql_query($sql) or die (mysql_error());
echo $sql;

Note that you're doing an || (or) in your main if() statement. This will will let your query go ahead even if only ONE of your arrays have values. You'd need to change it to && (and) comparison instead, which requires that ALL the arrays have values available.

For instance, if $gpa[$i] happens to be empty, then your query becomes

 SELECT * ... AND ipk='';

which is probably NOT what you want.

answered Jul 14 '11 at 15:15

Marc B

356,200
43
426
500

I've changed it into && but it still doesn't work. do you think this is because i put 2 arrays in a query? – aTFa Jul 15 '11 at 01:55
You can put a million arrays into the query and PHP will do what you tell it to. It all comes down to what the query string looks like after you build it. Garbage in, garbage out. Use my first construct (separate $sql variable) and LOOK at what you're building. – Marc B Jul 15 '11 at 14:06
Thank you, Solved! you are right, it's not the array. ive tried as what you suggested and i found out that array gpa=''. this is only because i typed the array name wrong. Thanks a lot :) – aTFa Jul 16 '11 at 13:14

score 1 · Answer 3 · answered Jul 14 '11 at 15:15

1

If you just want count (number of rows) you should do it like this...

$sql = mysql_query("SELECT COUNT(*) AS count FROM mytable WHERE study_period = '$period[$i]' AND ipk= '$gpa[$i]'") or die (mysql_error());    
$sql = mysql_fetch_assoc($sql);
$antecedent[$i]= $sql["count"];

answered Jul 14 '11 at 15:15

Dejan Marjanović

19,244
7
52
66

that works in phpmyadmin but that doesn't work when applied on php using two arrays in query. i think that two array can't be put in a query. do you think so? – aTFa Jul 15 '11 at 01:43

Using array in php query?

3 Answers3