Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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();
    ?>

  • #2
    New Coder
    Join Date
    Apr 2004
    Location
    Texas
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Birmingham was misspelled

    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");
    Jason B

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    577
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

    PHP Code:
    $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
    Last edited by Ökii; 05-09-2004 at 10:39 AM.
    Ökii - formerly pootergeist
    teckis - take your time and it'll save you time.

  • #4
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    577
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Ökii - formerly pootergeist
    teckis - take your time and it'll save you time.

  • #6
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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

  • #7
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    try using lowercase for querys
    ie

    PHP Code:
    $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

  • #8
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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

  • #9
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    have you checked the spelling of bham in database

  • #10
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    Sid

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

  • #11
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i just set up a test table and the below worked fine for me check your database entries for extra white space

    PHP Code:
    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(); 

  • #12
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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

  • #13
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    write a short script that grabs every row trims all fields then updates row should be pretty straight forward

  • #14
    New Coder
    Join Date
    May 2004
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Getting the Where to work in PHP\MYSQL

    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.

  • #15
    Regular Coder
    Join Date
    Mar 2004
    Posts
    115
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •