Please help unconfuse me

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
sandersonlj1529
Forum Newbie
Posts: 7
Joined: Fri Aug 07, 2009 7:45 am

Please help unconfuse me

Post 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
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Please help unconfuse me

Post 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.
Post Reply