1

When I run the code below when $entry = miami.com, I get the following error message:

SELECT COUNT(*) FROM #&*+ WHERE `site`
LIKE 'miami.com':You have an error in
your SQL syntax; check the manual that
corresponds to your MySQL server
version for the right syntax to use
near '' at line 1

It looks like I'm not correctly defining $table. Any ideas how I could do that?

Thanks in advance,

John

    $result = mysql_query("SHOW TABLES FROM feather") 
or die(mysql_error()); 


while(list($table)= mysql_fetch_row($result))
{
  $sqlA = "SELECT COUNT(*) FROM $table WHERE `site` LIKE '$entry'";
  $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
  list($isThere) = mysql_fetch_row($resA);
  if ($isThere)
  {
     $table_list[] = $table;
  }
}
Improve this question
9
  • Where's $table defined? What is $table? Commented Jun 16, 2009 at 5:27
  • Let's step back for a second. What exactly are you trying to do? Commented Jun 16, 2009 at 5:28
  • Well, I'm trying to show a list of all tables in the database "feather" that contain $entry. Commented Jun 16, 2009 at 5:32
  • I'm smelling sql injection vulnerabilities. Commented Jun 16, 2009 at 5:35
  • 2
    It looks loke your "show tables" query returns garbage. Can you post the output of the query? Commented Jun 16, 2009 at 5:44

6 Answers 6

Reset to default
1

if it were me debugging that i would see what

print_r(mysql_fetch_row($result));

outputs

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

7 Comments

what if you put ` around the table name SELECT COUNT(*) FROM $table ... I tried and i cant even create a table named #&*+
I just remembered that my first table name is called #&*+. I thought that was an error. I added this odd table name during development.
what happens if you rename/remove that table?
I tried putting ` around $table, and no error message. I'll print a few things out to see if you solved my problem.
Quick question: how would I print or echo $table_list[]?
|
1

I think you are using the list-language construct incorrectly:

Description

void list ( mixed $varname [, mixed $... ] )

Like array(), this is not really a function, but a language construct. list() is used to > assign a list of variables in one operation.

Example: 

$info = array('coffee', 'brown', 'caffeine');

// Listing all the variables
list($drink, $color, $power) = $info;
echo "$drink is $color and $power makes it special.\n";

Now, what you are trying to do is to fetch dynamic table-names (it seems). You do not need to use the list-function, since you can access the result as an array (you can define the appropriate indexes of the array that you are interested in and only assign them, but I think array access is much clearer):

while($row = mysql_fetch_assoc($result))
{
  $sqlA = "SELECT COUNT(*) FROM ${row['table']} WHERE `site` LIKE '$entry'";
  [...]
}

I am a bit curious though, do ALL the tables in your database feather have a column named site? Otherwise this query will fail, no matter how you format or refactor your code.

Improve this answer

4 Comments

Yes, all the tables have a column named "site." I will try your suggestion.
And you are also connected to the database feather?
Yes, I'm connected to feather. I tried this code: while($row = mysql_fetch_assoc($result)) { $sqlA = "SELECT COUNT() FROM ${row['table']} WHERE site LIKE '$entry'"; $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error()); list($isThere) = mysql_fetch_row($resA); if ($isThere) { $table_list[] = $table; } } and I got this error: SELECT COUNT() FROM WHERE site LIKE 'miami.com':You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE site LIKE 'miami.com'' at line 1
oh, you have to change 'table' into the correct index. do a var_dump($row); exit; before the sql and print the result
1

Actually, I recently recalled that my very first table name is indeed "#&*+." I added it deliberately during development

And you're wondering why your SQL fails? :)

Quote your table name because this one is by far not a table name that can be used literally.

Something like

"SELECT COUNT(*) FROM \"$table\" ...
Improve this answer

Comments

0

i think need to add MYSQL_ASSOC to line that do the loop

mysql_fetch_row($result,MYSQL_ASSOC)

the default is : MYSQL_BOTH

what mean that in the php loop you get the entry of the table name , and the entry of index like 0,1,2,...

Improve this answer

3 Comments

I appreciate the suggestion, but when I do that, I get this error: Warning: Wrong parameter count for mysql_fetch_row()
sorry i mistake i think u use fetch_array , u use fetch_row that is not problem
but try to do this : while ($tmp_tbl = mysql_fetch_array( $res_tbl )) { $curr_tbl = $tmp_tbl[0]; }
0

I am pretty sure your SHOW TABLES query is returning garbage. I was able to reproduce your problem by copying an existing table_name.frm to #&@.frm in the data folder for a local database. Make sure your database is not corrupt (meaning, try repair): http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

Improve this answer

1 Comment

Actually, my very first table name is called #&*+. During development, I added it on purpose to see if it would work
0

I think this is what you're after:

$result = mysql_query("SHOW TABLES FROM feather") or die(mysql_error()); 

while($table_row = mysql_fetch_row($result))
{
  $table = $table_row[0];
  $sqlA = "SELECT COUNT(*) FROM `" . mysql_escape_string($table) . "` WHERE `site` LIKE '" . mysql_escape_string($entry) . "'";
  $resA = mysql_query($sqlA) or die("$sqlA:".mysql_error());
  $isThere_row = mysql_fetch_row($resA);
  $isThere = $isThere_row[0];
  if ($isThere)
  {
     $table_list[] = $table;
  }
}

NOTE: variables inside your sql should be escaped. I don't use mySQL but I assume mysql_escape_string should work. There is another function, mysql_real_escape_string, that might be more appropropriate. You may want to read the docs for that.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.