SQL SELECT in PHP while loop

Question

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

It's rather hard to say why something doesn't work without you telling us exactly what it's supposed to do and what it actually does. For DB queries & results, example source and result data helps tremendously. What's the relation (if any) between the data in the four tables? You can probably fetch the results in a single query using joins. — outis
– outis, Commented Dec 28, 2010 at 1:39

Mark Byers · Accepted Answer · 2010-12-28 01:37:44Z

2

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%".

edited Dec 28, 2010 at 1:37

answered Dec 28, 2010 at 1:29

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

2 Comments

mr-sk Over a year ago

Yeah, that'd help ... = ]. Also, I think when doing inline SQL like this, you can attempt to make it more legible by using $sql = sprinft("...");

Jack Over a year ago

Thanks for the quick reply. I do have the SELECT in there. I just forgot to add it in this question.

Vinay Pai · Accepted Answer · 2010-12-28 01:30:33Z

1

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.

answered Dec 28, 2010 at 1:30

Vinay Pai

7,8171 gold badge20 silver badges28 bronze badges

Comments

mr-sk · Accepted Answer · 2010-12-28 01:40:28Z

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 . '%";';

Collectives™ on Stack Overflow

SQL SELECT in PHP while loop

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related