Jump to content

Need help with pulling last 3 entries from database with php


Colleen78

Recommended Posts

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:

[code=php:0]$sql = 'SELECT COUNT( * ) AS `Rows` , `item_id` FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` LIMIT 0, 30'[/code]

I'd appreciate any help, thank you.
Link to comment
Share on other sites

It would go something like this..

[code]
//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
          ";
}

[/code]

Hope that helps.
Link to comment
Share on other sites

Thanks so much bpops!

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

[code=php:0] 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";
[/code]

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.

Link to comment
Share on other sites

I changed the query line to this:

[code=php:0]$query = 'SELECT * FROM `tbl_item` GROUP BY `item_id` ORDER BY `item_id` DESC LIMIT 3';[/code]


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.
Link to comment
Share on other sites

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.com/item/Dragon_Hatchling_Figurine/217/c45

I don't know what to put in to get that.
Link to comment
Share on other sites

[quote author=ryanlwh link=topic=102398.msg406850#msg406850 date=1154386131]
oh i see what you mean.. sorry i misunderstood you. i think it should be something like
[code]$SSL_URL/item/$item_name/$item_id/[/code]

you'd have to select the item_name from the table. also, i'm not sure what c45 is..
[/quote]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
Link to comment
Share on other sites

Ok, the only problem now is underscores...

The link needs to be like this:
http://www.domain.com/item/Dragon_Hatchling_Figurine/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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[quote author=ryanlwh link=topic=102398.msg406872#msg406872 date=1154387506]
bpops, wrong order of arguments :)
[code]$item_name = str_replace(" ", "_",$item_name);[/code]
[/quote]

ah thanks, im still a bit of a newb myself :P
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.