CODEWITHSUNDEEP
phpmysqlloopswhile-loop
Jack
Jack

Reputation: 185

SQL SELECT in PHP while loop

I a set of tables and fields that I would like to select data from. I have tried the below code without success. Could any one explain to me why this does not work, and if possible, how to make it work.

$fields = "table1.field1, table2.field2, table3.field3, table4.field4";
$tables = "table1, table2, table3, table4";
$table = explode(', ', $tables); //explode the tables string
$field = explode(', ', $fields); //explode the fields string


$i=1;
while ($i<=4) { 
$sql = 'SELECT ' . $field[$i] . ' FROM ' . $table[$i] . ' WHERE ' . $field[$i] . ' LIKE "%' . $str . '%";';
$results = $readConn->query($sql);
$i++;
var_dump($results);
}

Upvotes: 1

Views: 1554

Answers (3)

mr-sk
mr-sk

Reputation: 13417

The SQL you're creating looks like this:

SELECT table2.field2 FROM table2 WHERE table2.field2 LIKE "%%";
SELECT table3.field3 FROM table3 WHERE table3.field3 LIKE "%%";
SELECT table4.field4 FROM table4 WHERE table4.field4 LIKE "%%";
SELECT  FROM  WHERE  LIKE "%%";

I don't know what you're setting $str to, so I don't have it included here. The SQL should run, it looks fine, except that last one ... You'll want to adjust your loop to be, <4, instead of <=4

Are you sure that you have a valid connection to the database? Are you getting back NULL or something as a result, or an error?

Also, item #2 in Mark's answer. Also, personally, I find this a lot more readable.

$sql = sprintf("SELECT %s FROM %s WHERE %s LIKE '%%s%'", $field[$i], $table[$i], $field[$i], $str);

than this

$sql = 'SELECT ' . $field[$i] . ' FROM ' . $table[$i] . ' WHERE ' . $field[$i] . ' LIKE "%' . $str . '%";';

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839074

Two things I can see:

1) You forgot the SELECT keyword:

$sql = 'SELECT ' .  $field[$i] . ' FROM ' ...etc...

2) In SQL strings should be escaped with single quotes, not double quotes. The result should resemble LIKE '%foo%' instead of LIKE "%foo%".

Upvotes: 2

Vinay Pai
Vinay Pai

Reputation: 7758

You're missing the SELECT keyword in the queries you generate. If you print out $sql it'll be obvious what the problem is. Incidentally, depending on where $str is coming from, you might be leaving yourself vulnerable to a SQL injection attack unless you escape it correctly.

Upvotes: 1

Related Questions