Posting SQL entries to home page

[Alumian]

Hi guys,

I'm attempting to incorporate a 'News' segment into my webpage and quite frankly I've created a monster.

Here is my code so far:

$sql = ("SELECT * FROM News ORDER BY ID desc LIMIT 5;");
$result = mysql_query($sql);
$row = mysql_fetch_array($result);
$count = 0;
while($count < 5)
{
echo "<div id=\"subject\" style=\"float:left;\">";
echo $row['Subject'];
echo "</div>";
echo "<div id=\"date\" style=\"float:right;\">";
echo "Date Posted: " . $row['Date'];
echo "</div><br /></div>";
echo "<div id=\"message\" style=\"font-size:16px;\">";
echo $row['Content'];
$count++;
}

 

As I'm sure you can imagine, all It's doing is posting this jazz 5 times, not only that, but it's posting the same content 5 times. So my question is this. How can I fix this so that it only posts as many entries as there are (up to a maximum of 5) to the front page, without repeating the output.

My other problem is that my DB isn't assigning the default timestamp to the Date field, here is the anatomy of my date field.

Field		Type				Attributes		Null		Default		Extra
Date		timestamp		on update CURRENT_TIMESTAMP	No	0000-00-00 00:00:00	ON UPDATE CURRENT_TIMESTAMP

 

thoughts?

[scootstah]

You need to put the "fetch" function into the while loop, like this:

while ($row = mysql_fetch_assoc($result)) {
        // ...
}

 

Notice also that I used mysql_fetch_assoc instead of mysql_fetch_array. Generally you do not need mysql_fetch_array(), and it isn't good to use it because you get back double the amount of data, and thus double the amount of memory (despite the fact that you almost always only use one set of data). The difference is that mysql_fetch_assoc() returns an associative array (like what you are using), and mysql_fetch_array() returns both an associative array and a numerical array. (If you want only a numerical array you can use mysql_fetch_row).

 

A lot of beginner tutorials use these functions incorrectly and instill bad habits.

 

EDIT: And about the timestamp thing, can you show us how you are inserting the data?

[Mahngiel]

You already limited the amount of articles, there's no reason to re-count in your while() loop.  Besides, your while should be

 

<?php
while($row = mysql_fetch_assoc($result) )
{
    // do stuff
}

[Alumian]

Thanks friends!

It works now

 

Next question.

Regarding the date in the DB, I read on a different thread on this forum that I'm not supposed to insert any value, but instead set the default to current_timestamp and let the DB do the work. That's not working T-T

My current insert statement for when the post is made, is as follows:

$sql = ("INSERT INTO News(Subject, Content)
VALUES('$_POST[subject]','$_POST[Content]')");

 

Thoughts?

[scootstah]

You can only use CURRENT_TIMESTAMP() if the field is of type "timestamp". If it is "datetime" (which it is) you have to manually insert the current date/time using NOW().

 

There are a couple things wrong with the code you posted though.

1. You are inserting unchallenged user input. NEVER DO THAT! Your script is vulnerable to SQL injection. You need to run any user input that interacts with the database through mysql_real_escape_string first.

 

2. When you are pulling data from an array you need to use quotes around the index. Currently your code will give a notice. It should be $_POST['Subject'] and $_POST['Content'].

 

Also, since you (presumably) didn't see that notice you are coding without the proper error reporting set. When developing you should be displaying ALL errors. To set the proper error reporting, place this at the top of your script:

ini_set('display_errors', 1);
error_reporting(-1);

[Alumian]

Thanks a lot mate

I'll get right on the escape strings

 

Edit:

One laaaast little thing. I remember in one webpage I developed a long time ago, someone had told me a way to make all variables that are $_POST, into escaped strings. This was quite some time ago, and since then I've forgotten. Would you know how to pull this off?

[scootstah]

They were possibly talking about magic quotes, which automatically adds slashes to all $_GET, $_POST, and $_COOKIE data. However, it does more harm than good. It doesn't take different character sets into consideration, and so it isn't as good as mysql_real_escape_string().

 

But, you can run a function against all of the elements of an array using array_map, array_walk or array_walk_recursive.

[Alumian]

Hmm, I believe it looked similar to this

foreach($array as $key => $value)

Buuuuuuuut I'm not entirely sure what the anatomy of this is, i.e. I'm assuming $array is the variable being checked, but I'm not sure what it means when it says as $key, and I guess => $value is saying it comes out as a new variable: $value.

Is that right?

[scootstah]

Yeah, you could do it that way if you wanted to create your own function. Something like:

function slashit($array)
{
$slashed = array();

foreach($array as $key => value)
{
	$slashed[$key] = mysql_real_escape_string($value);		
}

return $slashed;
}

 

However, the PHP functions I listed above basically do that for you, and array_walk_recursive will handle multi-dimensional arrays.

[Alumian]

Alrighty, thanks a lot for your help!