Jump to content

3 tables joining, output errors


Q695

Recommended Posts

I have a MySQL error that's working correctly, but the output is erroring.  What's wrong with how I'm calling the output?

I tried the two types of calls:

$row['table_3.table_3_field']

$row['table_3'].['table_3_field']

Link to comment
Share on other sites

Then it could pull one variable with the same name, or the other of the same name.  How do you guarantee that the proper variable name will be pulled every time, when it could pull the wrong one on a fluke.  right now it's working, but may not on an error.

Link to comment
Share on other sites

Yes, but that should throw a query error that you have ambiguous field name. But that can be solved by adding in the query something like:

SELECT table1.field1 as t1field1, table2.field1 as t2field1, table3.field1 as t3field1 .....

 

So then you can pull out the field you need with $row['t1filed1'], $row['t2field1'] and so on.

Link to comment
Share on other sites

You can do something like:

Select table1.*, table2.field as t2field, table3.field as t3field ..

 

Use the * for the table where you need all of the columns and from the other tables specify which fields you need and add "as" where needed (fields that have same name in more tables).

Avoid using the "*" when doing a select. Its not a good practice. Always fetch only the fields you need. Fetching more data then needed is never a good idea.

Link to comment
Share on other sites

It's not often that you require every field from the records in a table so just select the ones that you need. If you SELECT * when joining tables then you are guaranteed to have the same value selected twice.

 

The time taken by a query is proportional to the amount of data retrieved so for optimum efficiency keep the amount of data to a minimum.

Link to comment
Share on other sites

SELECT * , `items`.`name` as item_name

 

seems to be pulling out all of the variables in the item_name column, and setting them to item_name.

 

this is the like query:

'item_name' LIKE '%item_name%'

 

When it should be pulling things like: "necklace", and "ring", which are the phpmyadmin values.

 

when it print_r I get something like the following:

Array ( [auction_id] => 1 [seller] => 1 [bidder] => 1 [item] => 1 [price] => 2 [time] => 0 [id] => 1 [item_slot] => 1 [name] => Kevin => necklace.jpg [att_rate] => 5 [def_rate] => 5 [att] => 5 [def] => 5 [health] => 5 [level_req] => 1 [bio] => """''' [energy] => 2182 [energy_update] => 50 [experience] => 416 [to_level] => 50 [level] => 0 [att_rate_total] => 16 [def_rate_total] => 16 [att_total] => 16 [def_total] => 16 [health_total] => 16 [gold] => 500 [premium] => 5 [c_x] => 1 [c_y] => 1 [c_z] => 1 [location] => 1 [item_name] => necklace )

 

 

What am I doing wrong?

Link to comment
Share on other sites

How would we know. We don't know

  • your table structures
  • your query
  • what you expect

Sorry,but my crystal ball needs recharging

 

 

Now that your crystal ball is fixed, what's wrong with this variable loader

if(isset($_POST['name']) && $_POST['name']!=''){
 $name=$_POST['name'];
	 	$search="item_name LIKE '%$name%' AND";
} else {$name='';}

The AND is in the right place for how I'm writing the search algorithm.

Link to comment
Share on other sites

Is there a syntax error in the criteria builder if that's the first search item?

 

the search statement if there's added criteria:

$sql_auction="SELECT * , `items`.`name` as item_name
FROM auction, items, user_data
WHERE $search `auction`.`item` = `items`.`id`
AND `auction`.`seller` = `user_data`.`id`
ORDER BY time ASC
$limit";

Link to comment
Share on other sites

SELECT * , `items`.`name` as item_name FROM auction, items, user_data WHERE `auction`.`item` = `items`.`id` AND `auction`.`seller` = `user_data`.`id` ORDER BY time ASC LIMIT 0 , 10

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.