current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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);
}
share|improve this question
    
You the whole set of tables and fields? –  outis Dec 28 '10 at 1:37
    
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 Dec 28 '10 at 1:39

3 Answers 3

up vote 0 down vote accepted

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 . '%";';
share|improve this answer
    
Perfect! Thank you. –  Jack Dec 28 '10 at 12:27
up vote 2 down vote

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

share|improve this answer
1  
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("..."); –  Mr-sk Dec 28 '10 at 1:30
    
Thanks for the quick reply. I do have the SELECT in there. I just forgot to add it in this question. –  Jack Dec 28 '10 at 1:35
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.