MySQL Database Query problem

[djvk87]

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.

[djvk87]

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.

[james438]

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.

[djvk87]

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

[djvk87]

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:

"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:

$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.

[james438]

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.

[djvk87]

my code is as follows...

<?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.

[traq]

// 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

...
// 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.

[djvk87]

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)

[djr33]

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.