php quary help

[StealthRT]

Hey all, new to PHP so work with me :)

This is what i guessed it might be in PHP:

$link = mysql_connect('xxxx.com', 'xxxxx', 'xxxxx');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
//echo 'Connected successfully';

mysql_select_db("store1183") or die(mysql_error());

//echo 'Connected successfully';

$result = mysql_query("SELECT * FROM 'schedule" . "04-28-2008" . "'""");

if (!$result) {
die('Could not query:' . mysql_error());
}

if (!$result) {die('Could not query:' . mysql_error());}

while($row = mysql_fetch_array( $result )) {
echo $row['LastName'];
echo "<br>";
}

mysql_close($link);

However, this is the error i get with the current conversion code to PHP:

Could not query: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 ''schedule04-28-2008'' at line 1

Any help would be great!
David

[Fumigator]

Hey at least you put in error checking. 99% of beginning MySQL+PHP coders don't bother doing that. So you're way ahead of the game.

You're wrapping your table name in single quotes which is invalid SQL syntax. Table names do not require any quotes around them, unless they are poorly named and use the same value as a keyword (such as "from", or "date", or "where") and in that case the proper thing to do (aside from naming the table better) is enclose the name in backticks (the ` character).

So if you get rid of the quotes and as long as you have a table out there named schedule04-28-2008 then your query should work.

We can talk later about why you are apparantly creating new tables every day.

[StealthRT]

Thanks for the reply Fumigator:

It seems to still throw an error after i make the corrections as u stated above..

Could not query: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 '-21-2008' at line 1

And this is the updated code:

<?PHP
$Monday = date("m-d-Y", strtotime('next monday + 0 + week'));
$toSaturday = date("m-d-Y", strtotime('next saturday + 0 + week'));

$result = mysql_query("SELECT * FROM schedule" . $Monday . "");

if (!$result) {die('Could not query:' . mysql_error());}

while($row = mysql_fetch_array( $result )) {
echo $row['LastName'];
echo "<br>";
}

mysql_close($link);
?>

And yes, there is a table called schedule07-21-2008 :)

Any ideas?

David

[Fumigator]

Looks ok to me but there's something about the table name it doesn't like. Maybe it's the . "" at the end (which doesn't do anything for you ).

More generally, if you format your query as a variable before calling the mysql_query() function, you can easily display exactly what the query actually is and perhaps identify the problem. Something like:


<?PHP
$Monday = date("m-d-Y", strtotime('next monday + 0 + week'));
$toSaturday = date("m-d-Y", strtotime('next saturday + 0 + week'));
$query = "SELECT * FROM schedule" . $Monday . "";

$result = mysql_query($query);

if (!$result) {die('Query failed! Query text: $query<br>Error: ' . mysql_error());}

while($row = mysql_fetch_array( $result )) {
echo $row['LastName'];
echo "<br>";
}

mysql_close($link);
?>

[StealthRT]

Hum... this is what i get as the error...

Query failed! Query text: $query
Error: 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 '-21-2008' at line 1


:/

David

[StealthRT]

However, when i do the "echo $query . "<br>";" right after it shows me this:

SELECT * FROM schedule07-21-2008
Query failed! Query text: $query
Error: 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 '-21-2008' at line 1


David

[Fumigator]

Whoops I'm sorry, you must use double-quotes if those embedded variables are to be parsed.


if (!$result) {die("Query failed! Query text: $query<br>Error: " . mysql_error());}


At any rate, for some reason that table name is no good. AFAIK dashes are valid in table and column names so I don't know what the deal is. What version of MySQL are you using? If it's older then that may be the problem. Go ahead and enclose the table name in backticks (`, not ') to see if that solves the problem.

[StealthRT]

Freakin finally! :)

the ` seemed to work... and the sad part is, i just looked at my VB.net code that works using what i am trying to use with PHP, and i just noticed the ` in it as well.. go figure...

rst.Open("SELECT * FROM " & "`schedule" & Format(nextMonday, "MM-dd-yyyy") & "`", conn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic)


$query = "SELECT * FROM `schedule" . $Monday . "`";
$result = mysql_query($query);
echo $query . "<br>";
if (!$result) {die("Query failed! Query text: $query<br>Error: " . mysql_error());}

Any ways, thanks for the great help Fumigator! :)

David