Searching Stuffs in PMA (MySQL) Tables ["Inventory,cart_inventory,storage,guild_storage"]

[tmav94 1]

Search a specify item in PMA on tables "Inventory,cart_inventory,storage,guild_storage"

Like ...hm...
 

SELECT * FROM'Inventory,cart_inventory,storage,guild_storage' WHERE nameid = '%x%'; 

 

 

[Winterfox 83]

@@tmav94

 

What is the question or problem you need support with?

[Noil 1]

@@tmav94

 

Try this: 

 

SET @nameid = 2124;(SELECT `nameid`, `char_id` AS char_id, '-' AS account_id , '-' AS guild_id, `unique_id` as unique_id, 'inventory' as tablename FROM `inventory` WHERE nameid = @nameid)UNION(SELECT `nameid`, `char_id`, '-', '-', `unique_id`, 'cart_inventory' FROM `cart_inventory` WHERE nameid = @nameid)UNION(SELECT `nameid`, '-', `account_id`, '-', `unique_id`, 'storage' FROM `storage` WHERE nameid = @nameid)UNION(SELECT `nameid`, '-', '-', `guild_id`, `unique_id`, 'guild_storage' FROM `guild_storage` WHERE nameid = @nameid);

 

 

replace the nameid with your nameid

 

Greetings

Edited August 18, 2015 by Noil

[Garr 117]

@@Noil

If I remember correctly, making a query such as this will bring up an error in mysql about nameid being ambiguous. Iirc I had that once and just decided to check/replace those 4 tables at separate as I needed it for only one time.

Edited August 18, 2015 by Garr

[Noil 1]

Yeah I just tested it one sec I will edit soon

 

Edit: new one works like a charm

Edited August 18, 2015 by Noil

[Winterfox 83]

@@tmav94

 

You have to enclose table names in backticks ex.: `char`

and if you want to find only one row you have to group them.

sidenote: nameid --> type int(11) unsigned so you can't format it as string

I recommend this query

 

SELECT *

FROM `inventory`, `cart_inventory`, `storage`, `guild_storage`

WHERE nameid = 2629 --This is Meg for example just replace it whatever you want

GROUP BY nameid

 

 

Greetings

You only need those ticks if you use reserved words and only for them. If you use the char table or row for example which is a reserved word because of the char datatype that is used in the sql syntax when creating a new table.

 

In this case it is storage that is a reserved word. A full list can be found here: https://dev.mysql.com/doc/refman/5.5/en/keywords.html 

 

SET @nameid = 2629;SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = @nameidOR cart_inventory.nameid = @nameidOR`storage`.nameid = @nameidOR guild_storage.nameid = @nameidGROUP BY inventory.nameid

Edited August 18, 2015 by Winterfox

[Noil 1]

well I learned to use them

SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = cart_inventory.nameidAND cart_inventory.nameid = `storage`.nameidAND `storage`.nameid = guild_storage.nameidAND inventory.nameid = 2629GROUP BY inventory.nameid

Syntax is fine but I get no results

Edited August 18, 2015 by Noil

[4144 364]

well I learned to use them

 

 

SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = cart_inventory.nameidAND cart_inventory.nameid = `storage`.nameidAND `storage`.nameid = guild_storage.nameidAND inventory.nameid = 2629GROUP BY inventory.nameid

Syntax is fine but I get no results

 

This query return anything only if item present in all this tables

[Noil 1]

@@Winterfox

 

 

 

well I learned to use them

 

 

SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = cart_inventory.nameidAND cart_inventory.nameid = `storage`.nameidAND `storage`.nameid = guild_storage.nameidAND inventory.nameid = 2629GROUP BY inventory.nameid

Syntax is fine but I get no results

 

This query return anything only if item present in all this tables

 

Yeah right.

[Winterfox 83]

@@Winterfox

 

 

 

well I learned to use them

 

 

SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = cart_inventory.nameidAND cart_inventory.nameid = `storage`.nameidAND `storage`.nameid = guild_storage.nameidAND inventory.nameid = 2629GROUP BY inventory.nameid

Syntax is fine but I get no results

 

This query return anything only if item present in all this tables

 

Yeah right.

Thought the thing was to combine them based on if the user has the same stuff in his storage stuff.

 

SET @nameid = 2629;SELECT * FROM inventory, cart_inventory, `storage`, guild_storageWHERE inventory.nameid = @nameidOR cart_inventory.nameid = @nameidOR`storage`.nameid = @nameidOR guild_storage.nameid = @nameidGROUP BY inventory.nameid