Advanced Search

Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: MySQL Database Query problem

  1. #1
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Question MySQL Database Query problem

    I have a MySQL Database on my website that lists engine parts and am currently working on PHP code to dynamically make a table from Query results.

    My code for creating the tables for example are as follows:
    <?php
    mysql_connect("localhost", "username", "password") or die(mysql_error());
    mysql_select_db("djvkcom_ysp") or die(mysql_error());

    // Get specific items using query from the "ysp" table and stores them in result variable
    $result = mysql_query("SELECT * FROM ysp
    WHERE Heading='Anodes' AND `Description` LIKE '%2QM15%'") or die(mysql_error());

    // Sort the results into rows
    $row= mysql_fetch_array( $result );

    // Display the contents of the entry
    echo "<table border='1' style='background-color: #ffffff; text-align:center;'>";
    echo "<tr> <th>Image</th> <th>Item</th> <th>Engine Models</th> <th>Part No.</th> <th>Price</th> </tr>";

    // keeps getting the next row until there are no more to get
    while($row= mysql_fetch_array( $result )) {
    // Print out the contents of each row into a table
    echo "<tr><td>";
    echo "<img src=\"".$row['Image']."\">";
    echo "</td><td>";
    echo $row['SubHeading'];
    echo "</td><td>";
    echo $row['Description'];
    echo "</td><td>";
    echo $row['PartNo'];
    echo "</td><td>";
    echo $row['Price'];
    echo "</td></tr>";
    }

    echo "</table>";
    ?>


    The above code looks for parts with the heading 'Anodes' that contain the engine model '2QM15' in the description and then displays the results in a tabular format. When running the same query in phpMyAdmin's Search or Query tabs on the database this returns 2 results from my database. However when the above code is used on my website it only returns 1 result.

    I know the code is not at fault as it is only having this problem on that specific query. I have other pages with the same format of code but a different query showing multiple results. This makes me think it has something to do with the way the that particular description field is written. But I am confused as to why the Query works fine in phpMyAdmin but not when it is put on the webpage with the above code.
    Last edited by djvk87; 06-15-2010 at 01:59 AM. Reason: added additional information

  2. #2
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    Any idea? 109 views on post and not one reply
    Each post I have made on this forum has got almost no replies, and I would really like a solution to this problem or any insight to what it may be.

  3. #3
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    remove this line:
    $row= mysql_fetch_array($result);
    It is advancing the pointer one and is not doing anything productive anyway.

    The MySQL forum is not the most popular forum and the problem here is actually more of a php problem really. The odds of getting a response in this particular forum is certainly better than it has been, but it is difficult. Your best bet is to write your posts in such a way as to better attract readers. You did a pretty good job of stating your problem. A few suggestions though. Put your script in one of the CODE tags for readability, not the icode tag, which you used. The title of your thread could be more descriptive of your problem. You titled it MySQL Database Query problem, which is about as vague as titling it please help. Lastly, and this is really not your fault. your problem is more of a php problem rather than a MySQL problem, but really it could be posted in either forum.

    Other than that your post is not overly complicated. you stated your problem and desired result clearly as well as potential problem areas and did a fair amount of research into the problem yourself. You even annotated your code, which is great and shows what you are trying to do as well as your line of reasoning. Formatting your code better and adding a more descriptive thread title will certainly improve your chances of getting a response, especially in this forum. In the PHP and javascript forums it is much easier to get a response. I have found that MySQL is somewhat less interesting to the average coder though.
    Last edited by james438; 06-17-2010 at 04:20 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  4. The Following User Says Thank You to james438 For This Useful Post:

    djvk87 (06-18-2010)

  5. #4
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    Thanks for the pointers, I'll keep that in mind next time

  6. #5
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Exclamation MySQL Query Results on webpage differ to results in phpMyAdmin

    ok so try as I might I am still getting a problem with my MySQL Queries not working as they should.

    A query from within phpMyAdmin for an entry in my database with the heading of 'Gaskets' and the keyword '2YM15' (an engine model) in description shows one result. And from phpMyAdmin I can generate the PHP code for that query to preform it on my website.

    The code it gives me is as follows:
    Code:
    "SELECT * FROM `ysp` WHERE `Heading` = \'Gaskets\' AND `Description` LIKE \'%2YM15%\' LIMIT 0, 30 ";
    Now I tried to use that code on my page but it had a problem with the backslashes (syntax error) but once I removed them it no longer had any errors. And I removed the LIMIT section at the end as I don't want to limit the results at all.

    The end result is something like this:
    Code:
    $result = mysql_query("SELECT * FROM `ysp` WHERE `Heading` = 'Gaskets' AND `Description` LIKE '%2YM15%' "
    The problem now is it returns no results at all, instead of the one result it does in phpMyAdmin.

    I can not understand why this is happening.

    (On further investigation it seems what is happening is it is dropping 1 result from each query, if a query returns 2 results in phpMyAdmin then my code will only return 1 of them.
    And likewise if a query returns 1 result in phpMyAdmin then my code will return 0 results. I still can not explain why this is happening)

    The PHP code I am using to preform this query is freely available here and so if looking at that code helps you understand better then I hope you can help me. I have tried everything I can think of and I could really use the help, Thanks.
    Last edited by djvk87; 06-25-2010 at 04:39 AM. Reason: Additional Information

  7. #6
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    1,681
    Thanks
    78
    Thanked 89 Times in 87 Posts

    Default

    Can I see the code your query is a part of? At first glance I can't see anything wrong with your query, so I am thinking that your results are being manipulated somewhere later on in your script.
    To choose the lesser of two evils is still to choose evil. My personal site

  8. #7
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    my code is as follows...

    Code:
    <?php
    mysql_connect("xx", "xx", "xx") or die(mysql_error());
    mysql_select_db("xx") or die(mysql_error());
    
    // Get specific items using query from the "ysp" table and stores them in result variable
    $result = mysql_query("SELECT * FROM `ysp` WHERE `Heading` = 'Gaskets' AND `Description` LIKE '%2YM15%' ")or die(mysql_error());
    
    // Sort the results into rows
    $row= mysql_fetch_array( $result );
    
    // Display the contents of the entry 
    echo "<table border='1' style='background-color: #ffffff; text-align:center;'>";
    echo "<tr> <th>Image</th> <th>Item</th> <th>Engine Models</th> <th>Part No.</th> <th>Price</th> </tr>";
    
    // keeps getting the next row until there are no more to get
    while($row= mysql_fetch_array( $result )) {
    	// Print out the contents of each row into a table
    	echo "<tr><td>"; 
    	echo "<img src=\"".$row['Image']."\">";
    	echo "</td><td>";
    	echo $row['SubHeading'];
    	echo "</td><td>"; 
    	echo $row['Description'];
    	echo "</td><td>";
    	echo $row['PartNo'];
    	echo "</td><td>"; 
    	echo $row['Price'];
    	echo "</td></tr>"; 
    } 
    
    echo "</table>";
    ?>
    I know you previously told me to remove a line and I have a feeling it may be related to that but it is still there as the guide I have followed shows it that way and I am not quite sure how to remove it and keep it functioning as it seems '$row' is a vital variable for that code.

  9. #8
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,621
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    Quote Originally Posted by djvk87 View Post
    Code:
    // Sort the results into rows
    $row= mysql_fetch_array( $result );
    This line is giving you problems. I don't know why it is in the tutorial you've been following, but it's clearly out of place here.

    Edit:

    Actually, I think you're following two different examples given at different stages of the tutorial.
    The way they write tutorials on tizag is a little confusing like that sometimes



    Quote Originally Posted by djvk87 View Post
    Code:
    ...
    // keeps getting the next row until there are no more to get
    while($row= mysql_fetch_array( $result )) {
    ...
    This is the line you want. You don't need to declare $row earlier in the script (in fact, the problem is that you're not simply declaring it: you're running a function). Comment out the first line and see if it works.
    We Only Torture the Folks We Don't Like (You're Probably Gonna Be Okay)
    It's a Party in the CIA

  10. The Following User Says Thank You to traq For This Useful Post:

    djvk87 (06-25-2010)

  11. #9
    Join Date
    May 2010
    Location
    Rosebud West, Australia
    Posts
    33
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Default

    Thanks for the explanation I commented that line and you are correct, that fixed it, thank you very much. Now I just have to remove that line from a good 2,925 php files and I'm all good (Thankfully Notepad++ has a really good Replace function for that)

  12. #10
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,154
    Thanks
    260
    Thanked 690 Times in 678 Posts

    Default

    There's no reason at all that you should have that many files using the same code. If you do that, just use an include in each and borrow the code from a central location. If you need to change it, it's much easier then.
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

Bookmarks

Posting Permissions

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