Page 1 of 1

Please help unconfuse me

Posted: Wed Dec 09, 2009 3:19 am
by sandersonlj1529
I am quite new to php and I am using joomla and a component called JEA for a estate agents site they wanted to implement a "save property" function for the users so this is how I went about testing the theory (please excuse the code I know it could be better.)

I created a new table called jos_jea_saved this contains the following structure:

user_id | ref | property_id

this is the code I made to test listing the users saved properties

Code: Select all

 
// Display saved properties test
 
$user =& JFactory::getUser();
$query = "Select * from jos_jea_saved WHERE user_id = $user->id";
$result = mysql_query($query);
while($r = mysql_fetch_array($result)) {
$saved = split("[/]",($r["ref"]));
$saved_id = split("[/]",($r["property_id"]));
$newa = (array_combine($saved,$saved_id));
}
foreach( $newa as $key => $value){
    echo "<a href='http://lawrencesanderson.co.uk/romancity/index.php?option=com_jea&view=properties&Itemid=3&id=$value'>" . $key . "</a>"; 
    echo "<br>"; 
}
so the database with 2 users would look like this:

user_id | ref | property_id
62 | p1345/p6753/p1297 | 1/67/92
64 | p1330/p6489 | 10/65

Ok it works even if the coding isn't very good and I can easily add new properties to this data HOWEVER I just realised it is going to be almost impossible to delete a property from this... where have I gone wrong? :banghead:

Sorry this is so long, Thanks guys :D

Re: Please help unconfuse me

Posted: Wed Dec 09, 2009 4:12 am
by onion2k
I don't use Joomla so I can't help with the code, but the way you're saving properties is a bit daft (as you've discovered). You should be putting them into a separate table. Eg;

Code: Select all

user_saved_properties
 
user_id      property_id
62            1
62            67
62            92
64            10
64            65
 
Then, to get the properties that a user has saved you just have to do;

[sql]SELECT `property_id` FROM `user_saved_properties` WHERE `user_id` = $user_id[/sql]

To insert a new one;

[sql]INSERT INTO `user_saved_properties` (`property_id`, `user_id` ) VALUES ($property_id, $user_id)[/sql]

You should be careful with this though - it'd be possible to save the same property twice. The best solution to that is to create a UNIQUE index on the two columns. Then the query will fail if there's already a record with the user_id and property_id.

To delete one;

[sql]DELETE FROM `user_saved_properties` WHERE `property_id` = $property_id AND `user_id` = $user_id[/sql]

To delete them all;

[sql]DELETE FROM `user_saved_properties` WHERE `user_id` = $user_id[/sql]

It gets better too. If you add another column to the `user_saved_properties` table that stored the timestamp (or just the date) of when the user saved the property you can then pull out a list of their 5 most recently saved properties, or you can limit them to saving 20 properties by deleting the oldest one. You can let the user filter their favourites by joining the table to the properties table. You can do loads of cool stuff that you couldn't do if you're storing a string of ids.