Comparison between 2 tables

[manix]

Hello,

 

I have a "user's favorites" field which contains IDs of items glued with ":" and I need to return all items (located in another table) which is a piece of cake in php but I thought, hey wait a minute I think this can be done in with a single SQL line and I started looking at tutorials and stuff but still got nowhere, so actually what I have is

 

actually I think showing a php code of what I need done in sql would be more appropriate

 

<?php
$query = "SELECT `favorites` FROM `users` WHERE `id`='currentuserprofile'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
  {
  $favorites = explode(':', $row['favorites']);
  }
foreach($favorites as $value)
  {
  $query = "SELECT * FROM `items` WHERE `id`='$value'";
  $result = mysql_query($query);
  while($row = mysql_fetch_assoc($result))
    {
    echo stuff
    }
  }
?>

[kickstart]

Hi

 

It is possible, but a pretty nasty idea.

 

If you can split the list of favourites onto a separate table with one row per user and favourite.

 

To give you some idea of splitting up a string then take a look at this:-

 

http://www.phpfreaks.com/forums/index.php?topic=254226.msg1195156#msg1195156

 

That is splitting a string up (separated by ||) to get city names.

 

But I REALLY recommend that you do not do this.

 

All the best

 

Keith

[kickstart]

Hi

 

Further to the last post, you can do it like this (but really advise you not to).

 

SELECT UserName, CityArea
FROM (SELECT DISTINCT UserName, FavoriteId
FROM (SELECT UserName, SUBSTRING_INDEX( SUBSTRING_INDEX( favorites, ":", b.ournumbers ) , ":", -1 ) AS FavoriteId
FROM users a, ( SELECT hundreds.i *100 + tens.i *10 + units.i AS ournumbers FROM integers AS hundreds, integers AS tens, integers AS units)b) AS Deriv1
WHERE FavoriteId != "") Deriv2
INNER JOIN items ON Deriv2.FavoriteId = items.id

 

This is using a table of integers (one column, 10 rows with values 0 to 9) to get a load of numbers, and then using that number with substring_index to get all the possible indexes. Exclude the ones where the id is blank and use distinct to chop any duplicates.

 

Then join that against items to get the matching rows from items.

 

As you can see not readable, almost certainly hideously inefficient but technically possible.

 

FAR better to redesign the tables though.

 

All the best

 

Keith

[manix]

actually what I'm trying to do is not that complicated at all it's just I can't explain it, I guess. I'm here now

 

SELECT * FROM `items` WHERE `ID` LIKE  (SELECT `favorites` FROM `users` WHERE `id`='5')

 

which is pretty much what I was looking for except I can't manage to figure out how to find the item's ID in the favorites cause I don't know how to put the percent signs around  (SELECT `favorites` FROM `users` WHERE `id`='5') which is supposed to be like '%(SELECT `favorites` FROM `users` WHERE `id`='5')%'.  Can you help me with that?

 

EDIT:

 

This is error-free but doesn't return anything

 

SELECT * FROM `items` WHERE `ID` LIKE ('%'+(SELECT `favorites` FROM `users` WHERE `id`='5')+'%')

 

 

EDIT2:

 

Okay, new discovery, I changed the `favorites` glue to " " instead of ":" and now it returns the FIRST favorite..

[kickstart]

Hi

 

Don't think you can do that but it will mess up with 1 matching 10, 11, 12, etc.

 

If you could you would want something like:-

 

SELECT * FROM `items` WHERE CONCAT('%',`ID`,'%') LIKE  (SELECT `favorites` FROM `users` WHERE `id`='5')

 

Or doing it using a JOIN

 

SELECT * 
FROM users
INNER JOIN items
ON  CONCAT('%',items.ID,'%') LIKE favorites
WHERE users.id='5'

 

Neither work (without looking into it too much, don't think you can concatenate in wildcards to a column value to use a LIKE).

 

All the best

 

Keith

[manix]

Well.. Guess I'll just do it in php, at least I learned alot (based on what I knew before) about sql now.

 

Thanks Keith

[manix]

Holy crap here it is:

 

SELECT * FROM `items` WHERE (SELECT `favorites` FROM `users` WHERE `id`='5') LIKE CONCAT('%:',`ID`,':%')

 

and notice I have colons before and after the percents so it doesn't mess 1 with 10,11,12

[kickstart]

Hi

 

Interesting that it manages something but that won't work as it will not match the first or last items in the : separated list (unless you have a colon at the start and end of the list).

 

If you do use a loop then do it like this:-

 

<?php
$query = "SELECT `favorites` FROM `users` WHERE `id`='currentuserprofile'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
$favorites = explode(':', $row['favorites']);
$query = "SELECT * FROM `items` WHERE `id` IN (".implode(",",$favorites).")'$value'";
$result = mysql_query($query);
while($row = mysql_fetch_assoc($result))
{
	echo stuff
}
}
?> 

[manix]

No no the sql query works perfectly, as the favorites' default value is ":" and every time a new item is added I add another colon so it becomes ":item1:item2:item3:"

 

here's the update line: mysql_query("UPDATE `users` SET `favorites`= CONCAT(`favorites`,'$id:') WHERE `id`='$userid'");

 

therefore when an item is removed the colon after it is removed too so the sequence remains the same

 

It's working perfectly brilliant, thank you again!