...

View Full Version : Getting the Where to work in PHP\MYSQL



paragtim
05-09-2004, 10:06 AM
If anyone can tell me - in simple English where I am going wrong with this I would really appreciate it.

The script at the bottom of this thread returns all the records without a problem. But I only want to return the records where the town matches the right hand side of the WHERE statement. for example:

$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHERE town = "Birmingaham" order by town");

Whenever I run this there are no records returned. I have tried it without any quotes, with single quote and with double quotes. I'm must be doing something fundementally wrong but can't work it out.


The full script is shown below

/* Accessing SQL-Server and querying table */
MYSQL_CONNECT($server, $user, $password) or die ( "<H3>Server unreachable</H3>");
MYSQL_SELECT_DB($database) or die ( "<H3>Database non existent</H3>");
$result=MYSQL_QUERY( "SELECT name, town, county FROM $table order by town");
$num_rows = MYSQL_NUM_ROWS( $result );


print "The are $num_rows rows in the table<p>";
/* Output data into a HTMl table */
echo "<table border=\"1\" align=center width=50%";
echo "<tr>";
echo "<div color=\"#ffff00\">";
while ($field=mysql_fetch_field($result)) {
echo "<th>$field->name</A></th>";
}
echo "</font></tr>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
for($i=0; $i < mysql_num_fields($result); $i++) {
echo "<td align=center>$row[$i]</td>";
}
echo "</tr>\n";
}
echo "</table><BR><BR>";

/* Close SQL-connection */
MYSQL_CLOSE();
?>

black3842
05-09-2004, 10:36 AM
you're gonna hate this..
Birmingham was misspelled, and use single quotes.

$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHERE town = 'Birmingham' order by town");

Íkii
05-09-2004, 10:37 AM
Have you tried it with spelling Birming[a]ham properly?

Other than that, your syntax is valid, presuming you are not using a reserved name as $table - maybe try backticking just in case



$result = mysql_query("SELECT `name`, `town`, `county`
FROM `".$table."`
WHERE `town` = 'Birmingham'
ORDER BY `town`");


You could also try dumping the copy-paste sql into phpmyadmin or the like, to test whether rows were returned.

EDIT : the error was in surrounding "Birmingham" with double quotes - that would truncate the SQL string too early - use single quotes within double quoted strings

paragtim
05-09-2004, 11:37 AM
I changed the name to avoid spelling errors and tried dumping the Query into phpmyadmin - as Okii suggested. Please see the error message shown below.

Still open to suggestions

SQL-query :

$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHERE town = 'Bath' order by town")

MySQL said:


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 '$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHE

Íkii
05-09-2004, 03:13 PM
to use near '$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHE

implies that the entire line (including the $result = ....) is being interpreted as SQL.
Just dump the actual SQL into phpmyadmin - eg the "SELECT ......" bit

paragtim
05-09-2004, 06:29 PM
As suggested by Okii I put the Query into phpadmin and the following success message was returned:-

Your SQL-query has been executed successfully

SQL-query :
SELECT name, town, county FROM coachtable03 WHERE town = 'Bath' order by town LIMIT 0, 30

I then pasted this into the MYSQL_QUERY line, between the double quotes, and re-ran the page. Still no output. If the query is correct then howcome there is no putput. I'm getting really confused now

sidney
05-09-2004, 07:00 PM
try using lowercase for querys
ie


$result=mysql_query( "SELECT name, town, county FROM $table WHERE town = 'Birmingaham' order by town");
$num_rows = mysql_num_rows( $result );

and i dont see where $table is set

paragtim
05-09-2004, 07:35 PM
Tried as you suggested, changing to lower case and I have also changed $table to the table name (coachtable03). Still no joy. Displays great as long as I don't use a WHERE clause

sidney
05-09-2004, 07:49 PM
have you checked the spelling of bham in database

paragtim
05-09-2004, 08:32 PM
Sid

Yes, Even changed the WHERE to another column. Still no joy. Prints fine without the WHERE. Nothing with a WHERE. I'm stumped!!!!!!

sidney
05-09-2004, 08:52 PM
i just set up a test table and the below worked fine for me check your database entries for extra white space


require("connect.php");
$table="test";
$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHERE town='birmingham' order by town");
$num_rows = MYSQL_NUM_ROWS( $result );


print "The are $num_rows rows in the table<p>";
/* Output data into a HTMl table */
echo "<table border=\"1\" align=center width=50%";
echo "<tr>";
echo "<div color=\"#ffff00\">";
while ($field=mysql_fetch_field($result)) {
echo "<th>$field->name</th>";
}
echo "</font></tr>";
while($row = mysql_fetch_row($result)) {
echo "<tr>";
for($i=0; $i < mysql_num_fields($result); $i++) {
echo "<td align=center>$row[$i]</td>";
}
echo "</tr>\n";
}
echo "</table><BR><BR>";

/* Close SQL-connection */
MYSQL_CLOSE();

paragtim
05-10-2004, 06:59 AM
Sid,

You are a God amongst men. May you always walk with a youthfull step and get the sexual partner of your choice (as long as it not my wife or my daughters!!!).

The problem is whitespace in the table. Any quick fixes that you, or other lesser gods might know about to remove white space from a MySQL Table?

Thank you all for your help

sidney
05-10-2004, 09:04 AM
write a short script that grabs every row trims all fields then updates row should be pretty straight forward

paragtim
05-10-2004, 05:31 PM
I'd like to but I think that is beyond my skills at the moment. I'll have to do it the old fashioned way.

sidney
05-10-2004, 05:39 PM
give me you table lay out and field types

and ill write it for you wont be very long

let us know what fiellds are affected with white space

and how many rows in data base

sidney
05-10-2004, 07:21 PM
http://dev.mysql.com/doc/mysql/en/String_functions.html

try the trim function of mysql



$result=MYSQL_QUERY( "SELECT name, town, county FROM $table WHERE trim(town)='birmingham' order by town");
$num_rows = MYSQL_NUM_ROWS( $result );

paragtim
05-11-2004, 06:37 PM
Hi Sid,

Sorry for the delay in getting back. Power cut corrupted the PC.

I just tried that peice of code and it worked a dream. As a fix it is great.

When I read the last but one post I was going to send up the tables etc, however I still need to learn how to clean the database - white spaces and Tabs seem to be everwhere.

Is it possible you, or someone, could point me in the direction of how to remove them from the table via code instead of amending the records by hand. Sort of pulling the data, applying the trim function and sending it back. I have no idea how to start, but it seems it would be a good skill to master.

sidney
05-11-2004, 07:22 PM
this should do it its set to run with any size table and a id field you will need to change the value of the id field to match one of your table fields this field will not be updated so if its affected run script twice changing $idfield to another field the second time and $tablename needs to be name of table



$idfield="id";
$tablename="test";
require("connect.php");
$query = "SELECT * FROM ".$tablename;
$result = mysql_query($query);
while($row=mysql_fetch_assoc($result))
{
$update="UPDATE ".$tablename." SET ";
foreach($row as $key => $val)
{
if($key!=$idfield)
{
$val1=trim($val);
if(!is_numeric($val1))
{
$update.= $key." = '".$val1."', ";
}else{
$update.= $key." = ".$val1.", ";
}
}
}
$update=substr($update,0,-2);
$update.=" WHERE ".$idfield." = '".$row[$idfield]."'";
if(mysql_query($update))
{
echo"updated--".$row[$idfield]."--";
}
echo $update."<br>";
}

paragtim
05-16-2004, 08:43 PM
Thanks Sid,

Sorry for the delay - Life got in the way of reality.

Got it working - Your script was great - I had problems with the Trim syntax and Tabs.

Many many thanks - Now on to the next problem



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum