Jump to content


Photo

Need help with pulling last 3 entries from database with php


  • Please log in to reply
21 replies to this topic

#1 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 04:06 AM

I have a shopping cart programmed with php, I am making a custom homepage and want to grab the 3 latest items added and post them on the homepage.

I am a beginner so I need the full code to pull it. We don't have a timestamp so the cart company just said to make it pull the three highest ID's.

This is the php code for that table in the database:

$sql = 'SELECT COUNT( * ) AS `Rows` , `item_id` FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` LIMIT 0, 30'


I'd appreciate any help, thank you.

#2 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 31 July 2006 - 04:57 AM

$sql = 'SELECT COUNT( * ) AS `Rows` , `item_id` FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3'
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#3 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 05:11 AM

Thanks Crayon! As I stated, I am new to this. I need all the code. I don't know how to put it together to make it work.

:D

#4 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 06:01 AM

It would go something like this..

//Connect to Database
	$host="mysql.yourhost.com";
	$database = "your_database";
	$user = "your_username";
	$password = "your_password";
	$connection = mysql_connect($host,$user,$password)
		or die ("couldn't connect to server");
	$db = mysql_select_db($database,$connection)
		or die ("Couldn't select database");

//Specify the query
	$query = 'SELECT COUNT( * ) AS `Rows` , `item_id` FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3';

//Execut the query
	$result = mysql_query($query)
       or die ("Error. Unable to perform query.");

//Loop through all the results, displaying each one
while ($row = mysql_fetch_array($result))
	  {
	     extract($row);
           echo "
           //HTML goes here.. use $value for the values where the value name is just the name of the column in the mysql table. for example:
           // $username: $info  --  Date:  $date
           ";
}


Hope that helps.

#5 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 09:13 PM

Thanks so much bpops!

Ok, I edited the echo part but it's coming up empty, this is my code.

echo "<td>\n";
	echo "$item_name\n";
	echo "<a href=\"$SSL_URL\"><img src=\"$SSL_URL/item_images/$thumbnail_image\" border=\"0\" /></a>\n";
	echo "$short_description";
	echo "</td>\n";

The td's for the table are showing up in the source just empty. I need to call the items name, image and description and link the image to it's page. The variables in the echo's are the row names in the table.



#6 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 10:09 PM

I changed the query line to this:

$query = 'SELECT * FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3';


Now it works, I just need to figure out how to link the images to their pages, if anyone can help, that would be great.

#7 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 10:11 PM

Glad you got it working!

Can you please explain a little more the linking problem you're now having?

#8 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 10:34 PM

Hey!

I don't know how to link at all. I only put this in the link so far, $SSL_URL which gets the domain root, I need it to add on the rest of the items link.

The items link like so:
http://www.domain.co...igurine/217/c45

I don't know what to put in to get that.

#9 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 31 July 2006 - 10:37 PM

well don't you already have the image src??

$SSL_URL/item_images/$thumbnail_image
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#10 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 10:46 PM

Yes, and the image displays correctly, does that same code somehow tie into the products page link so I can have the visitor click the thumbnail and be taken to the product page?

#11 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 31 July 2006 - 10:48 PM

oh i see what you mean.. sorry i misunderstood you. i think it should be something like
$SSL_URL/item/$item_name/$item_id/

you'd have to select the item_name from the table. also, i'm not sure what c45 is..
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#12 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 10:53 PM

oh i see what you mean.. sorry i misunderstood you. i think it should be something like

$SSL_URL/item/$item_name/$item_id/

you'd have to select the item_name from the table. also, i'm not sure what c45 is..

Thanks ryanlwh! You guys are so smart here! :)

I am not the best at explaining, sorry about that. The cart uses search engine friendly urls so hopefully that doesn't make it too hard to figure out how to get it work, I'll try your code and play around with it a bit and report back.

Thanks so much!

edit: I just looked in the DB, c45 is the category id! :D

#13 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 31 July 2006 - 10:57 PM

no problem. we're here to help. search engine friendly urls are fine. i'm thinking the last part "c45" is probably a category id
Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#14 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 31 July 2006 - 11:03 PM

Ok, the only problem now is underscores...

The link needs to be like this:
http://www.domain.co...igurine/217/c45

and right now I have it doing this:
http://www.domain.com/item/Dragon Hatchling Figurine/217/c45

Any idea how to get the underscores in? Thanks again!

#15 pixy

pixy
  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 31 July 2006 - 11:04 PM

Maybe do a str_replace for spaces with an underscore? I don't know if that would work, just a guess. Then when you have it get it from the database you'd need to take them out again and replace them with spaces.

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!


#16 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:08 PM

further info:
http://us2.php.net/str_replace

example:
$item_name = str_replace($item_name, " ", "_");

I think that might work..

#17 ryanlwh

ryanlwh
  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 31 July 2006 - 11:11 PM

bpops, wrong order of arguments :)
$item_name = str_replace(" ", "_",$item_name);

Please use EDIT * 100...
Please use
or [php] * 1000...

PLEASE READ THE POSTED SOLUTIONS CAREFULLY * 1000000...

#18 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 31 July 2006 - 11:17 PM

bpops, wrong order of arguments :)

$item_name = str_replace(" ", "_",$item_name);


ah thanks, im still a bit of a newb myself :P

#19 Colleen78

Colleen78
  • Members
  • PipPipPip
  • Advanced Member
  • 66 posts
  • LocationCanada

Posted 01 August 2006 - 12:00 AM

Ok, that's working now! Thanks so much! What do I owe you guys?!  :-X ;D

#20 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 01 August 2006 - 12:04 AM

After becoming an expert, come back and help someone else.. I think that's how this site's supposed to work :P




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users