mySQL/php query errors

Tethys

Hello all 🙂

I've just started dabbling in PHP and mySQL, and I've managed to tie my brain in a knot.

I've tried to look for answers from www.mysql.com but I don't really understand what the manual talks about over there, and it doesn't help with the proper php syntax of course.

Anyway, here's my problem.
I'm trying to make a user-submitted database table of equipment for my EverQuest guild. Currently, I have a function to print the items that are in each category. The category is defined by an array of item types, which are stored in the table as type.

So I tell it

$link=mysql_connect($host,$user,$password);
	$cat_count=count($category);
	for ($n=0; $n<$cat_count; $n++) {
	$item_type=$category[$n];
	$query="SELECT*from $table_name where type='$item_type' ";
	$result = mysql_db_query($db_name,$query,$link);

And then tell it to print various things. That works just fine as long as there is information for an item of $item_type. However it prints all kinds of errors if there isn't.

Can I ask it to skip to the next item in the array if there is no information in the table?

One more thing. My queries all work if the file is in a regular subfolder of my site. However, the guild is in a subdomain that points to a subfolder, and I can't get queries to work from there. They don't give an error, but no information is sent to the database table.

I can print more code, etc or provide links, but I didn't want to make this even longer than it already was.

Thank you so much for your help.

theeper

Seems like you could just do an if() on the $item_type variable and not do the query if there is no value.

Maybe something like this ...

$link=mysql_connect($host,$user,$password);
    $cat_count=count($category);
    for ($n=0; $n<$cat_count; $n++) {
    $item_type=$category[$n];

if ($item_type != "") {
    $query="SELECT*from $table_name where type='$item_type' ";
    $result = mysql_db_query($db_name,$query,$link);
}

BTW, what server do you play on ?

Tethys

I'm on Xev server. The guild is Chevaliers de la Lune (not a French guild, just French name).

Unfortunately, with my current setup, the query would run all the time, since I defined an array for each category consisting of where the items are worn. Then called Print_Item($category).

$item_name will always be defined, since that is the selector for the search.

I tried to enter a "blank row" in the table, where only the item type was defined, so hopefully it would print blanks under each category instead of giving me errors. It did not do that, so I'm wondering if there's something else wrong with my code.

The morbidly curious can take a look at it here:
http://www.dragon-sea.net/anachron_test/armoury-test.php
The format is not what it's supposed to look like, since all the css files are in another directory, but you get the idea.

http://www.dragon-sea.net/anachron_test/display_armoury.php
shows that database queries do work.. and so do limited queries.

Sigh. I'm probably going about this in the wrong way....
Suggestions are welcome 😃

Edited to fix URLs

theeper

I don't know what you mean by this

Unfortunately, with my current setup, the query would run all the time, since I defined an array for each category consisting of where the items are worn. Then called Print_Item($category).

$item_name will always be defined, since that is the selector for the search

what errors are you getting?

Tethys

Thank you for being so patient with me...

Here is the function that I wrote to print all the items in a...hmm. I just thought of something. Well I'll work on that, but I'll also show you what I have at the moment.

function Print_Items ($category) {
	$host="localhost";
	$user="***";
	$password="***";
	$db_name="***";
	$table_name="armoury";

$link=mysql_connect($host,$user,$password);
$cat_count=count($category);
for ($n=0; $n<$cat_count; $n++) {
$item_type=$category[$n];
print("item_type is $item_type<br>\n");
$query="SELECT*from $table_name where type='$item_type' ";
$result = mysql_db_query($db_name,$query,$link);

print("<h4>$item_type</h4>\n");
print("<table><tr align=center valign=top>\n");
print("<td align=center valign=top>Item</td>\n");
print("<td align=center valign=top>Stats</td>\n");
print("<td align=center valign=top>Price</td>\n");
print("<td align=center valign=top>Character</td>\n");
print("<td align=center valign=top>Best Time to Catch Seller</td>\n");
print("</tr>\n");

//fetch results

while ($row=mysql_fetch_array($result)) {
	print("<tr align=center valign=top>\n");
	print("<td align=center valign=top>$row[item_name]</td>\n");
	print("<td align=center valign=top>$row[stats]</td>\n");
	print("<td align=center valign=top>$row[price]</td>\n");
	print("<td align=center valign=top>$row[char_name]</td>\n");
	print("<td align=center valign=top>$row[best_time]</td>\n");
	print("</tr>\n");
	}

mysql_close($link);
print("</table>\n");
print("<a href=\"#top\">[Return to Top]</a><br><br>\n");}
} //end of function

Here's where I call the function:

<hr>
<?php 
$armor=array("Head","Neck","Face","Chest","Shoulders","Arms","Wrist","Hands","Back","Legs","Feet","Waist");
$weapons=array("1 hand slash","2 hand slash","1 hand blunt","2 hand blunt","Piercing","Hand to Hand","Range");
$containers=array("Containers");
$jewelry=array("Fingers","Wrist","Ear","Neck");
$misc=array("Miscellaneous");
print("<h3>Armor</h3>\n");
Print_Items($armor);
print("<hr>\n");

 ?>

I have a thought where I should just call the function with each item type rather than trying to pass an array. I think that I could then do what you suggested earlier. I'll try that, but please take a look at this and tell me how ugly it is.

Here are the errors that I get:

Warning: mysql_db_query(): 1 is not a valid MySQL-Link resource in /home/mensch/www/anachron_test/armoury-test.php on line 17

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/mensch/www/anachron_test/armoury-test.php on line 30

Warning: mysql_close(): 1 is not a valid MySQL-Link resource in /home/mensch/www/anachron_test/armoury-test.php on line 40

These repeat over and over.

Tethys

Ergh, horizontal scroll.. Sorry about that.

Theeper thank you so much! I solved it after pondering your suggestion. I was trying to get too fancy in the function and it wasn't working. Instead of passing an array of item types to the print function, I passed each type individually. Then ran this if conditional:

$query="SELECT*from $table_name where type='$item_type' ";
	if ($query) {
	$result = mysql_db_query($db_name,$query,$link);
	print("<h4>$item_type</h4>\n");
	print("<table><tr align=center valign=top>\n");
	print("<td align=center valign=top>Item</td>\n");
etc etc

It still prints some things that I didn't intend, but it doesn't give any errors at all. I'm going to play with it some more until it's exactly what I want, but "yay!"

I feel way too happy about this, but I hate getting stuck!
😃 😃 😃 😃 😃