3 tables joining, output errors

[Q695]

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']

Edited May 22, 2013 by Q695

[Barand]

Don't include the table name in the column key names

 

$row['table_3_field']

[Q695]

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.

Edited May 22, 2013 by Q695

[Barand]

If you have more than one column with same name then your query is wrong

[Q695]

the columns are named the same thing in different tables.

[Dathremar]

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.

[Q695]

Will it work if I'm doing a select *?

[Dathremar]

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.

[Q695]

Why is it a bad idea when using all the fields?

[Barand]

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.

[Q695]

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?

[Jessica]

I see necklace.... What is the problem?

[Q695]

I see necklace.... What is the problem?

It outputs nothing when the statement has "WHERE 'item_name' LIKE '%neck%' AND".

[Barand]

What am I doing wrong?

 

How would we know. We don't know

• your table structures
• your query
• what you expect

Sorry,but my crystal ball needs recharging

[Jessica]

Probably because 'item_name' is a string and is not at all like 'neck'

[Barand]

column names do not have quotes

[Q695]

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.

[Jessica]

Once again - how should we know? Is there a reason you are being so secretive?

[Q695]

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

Edited May 24, 2013 by Q695

[Q695]

The error is: can not generate result Unknown column 'item_name' in 'where clause'

 

The variable item_name is there.

[Barand]

What we, and you, need to see is the result of

 

echo $sql_auction;

 

so we can see exactly what is being executed

Edited May 24, 2013 by Barand

[Q695]

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

[Q695]

I solved it by calling one of the tables with identical column names where it would be used