Need help from the PHP gods

Adding coding to my page

[Mtlinfo]

Hi guys,

First of all, I must say that I am not a PHP programmer but I'm not too bad in anylizing code and I spent the week-end learning with basic PHP and MySQL tutorials.

I learned how to create a db, tables and fields using both a php page and also using the PHPAdmin panel.

Yesterday I learned how to insert data (via a basic html form) and the PHP code to retrieve that info on a page.

For you it's kid's stuff but me it's a huge step hehe. :-)

Alright, here's what I have in mind:

I will send you the code that I have on my retrieve.php page and ask you to tell me what to modify in the code so that it looks the way I want it.

So here's the link to the page where you will see the look of how the php code display my db info and just below it the way I want things to be displayed.

As you can see, the retrieve code that I have sticks the db data all in one big html table while I want it to be displayed where 1 record will have it's own table.

The second thing you will see is that on each record you can see the date od entry or update (unix_timestamp) but there are not listed orderly by date while below the 3 records are in order (18, 31, 55).

So here's the code that I have on my retrieve.php page so please let me know what do I have to modify so that it looks the way I want it.

Thanks a million,

Richard

- begin code -

<?php

$connection = mysql_connect ("localhost", "myusername", "mypassword");
if ($connection == false){
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}

$query = "select * from email_info";
$result = mysql_db_query ("my_db", $query);

if ($result){
echo "<table border=1>";
echo "<tr><td><b>Full Name</b></td><td><b>Email Address</b></td><td><b>Age</b></td><td><b>Updated</b></td></tr>";

$numOfRows = mysql_num_rows ($result);
for ($i = 0; $i < $numOfRows; $i++){
$name = mysql_result ($result, $i, "fullname");
$email = mysql_result ($result, $i, "email_address");
$age = mysql_result ($result, $i, "age");
$updated = mysql_result ($result, $i, "updated");

echo "<tr><td>$name</td><td>$email</td><td>$age</td><td>$updated</td></tr>";
}

echo "</table>";
}
else{
echo mysql_errno().": ".mysql_error()."<BR>";
}

mysql_close ();

?>

[edited by: jatar_k at 9:16 pm (utc) on Mar. 15, 2004]
[edit reason] no personal urls thanks [/edit]

[MrSpeed]

To sort by date use this
$query = "select * from email_info order by updated";

[Mtlinfo]

Thanks MrSpeed (now I know why you are called that way) ;-)

It works fine thank you but what is the string to make them listed in decreasing order (from the latest updated at the top to the oldest updated at the bottom)?

Mllinfo

[MrSpeed]

For the other part of the code you just need to move some of your table tags inside the loop so you are always making a new table with each row.

I think the code wil work as is but I did not test it. I am far from a PHP guru ;)

<?php
$connection = mysql_connect ("localhost", "myusername", "mypassword");
if ($connection == false){
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}
$query = "select * from email_info order by updated ";
$result = mysql_db_query ("my_db", $query);
if ($result){
$numOfRows = mysql_num_rows ($result);
for ($i = 0; $i < $numOfRows; $i++){
$name = mysql_result ($result, $i, "fullname");
$email = mysql_result ($result, $i, "email_address");
$age = mysql_result ($result, $i, "age");
$updated = mysql_result ($result, $i, "updated");

echo "<table border="1">";
echo "<tr><td><b>Full Name</b></td><td>$name</td></tr>";
echo "<tr><td><b>Email Address</b></td><td>$email</td></tr>";
echo "<tr><td><b>Age</b></td><td>$age</td></tr>";
echo "<tr><td><b>Updated</b></td><td>$updated</td></tr>";
echo "</table>";
}
echo "<p>";
}
else{
echo mysql_errno().": ".mysql_error()."<BR>";
}
mysql_close ();
?>

[MrSpeed]

$query = "select * from email_info order by updated DESC";

[Mtlinfo]

Hi MrSpeed,

I get a blank page when I load the php page with your modified code!

Any ideas what's the bug?

Mtlinfo

[MrSpeed]

I told you I was no guru!

I'm sure it's something stupid.

Try to echo out $numOfRows to see if the query returns anything.

You should also be able to test the query in your db admin area.

[Mtlinfo]

Not sure exactly what you meant by echo out but I tried it with the echo string and the "" too and also deleted the entire line of code but anyway it doesn't work.

Anyone out there can look at MrSpeed code above and see where the bug is?

Thanks

Mtlinfo

[MrSpeed]

What I meant is I like to sometimes print what the different variables are so I just print them out. This is sometimes called tracing.

$result = mysql_db_query ("my_db", $query);
echo $result;
echo mysql_num_rows($result);
//you can exit here if you like while tracing
//exit();

if ($result){
$numOfRows = mysql_num_rows ($result);
etc..
etc..
etc..

[Mtlinfo]

GOTCHA....

It was the code line

echo "<table border="1">";

We needed to replace the 2 "" with 2 '' around the number 1 ;-)

echo "<table border='1'>";

Thanks again for the tip

Mtlinfo

[MrSpeed]

change the
echo "<p>";
to
echo "<p>&nbsp;";

You may have to escape some of the characters in &nbsp; like this
echo "<p>\&nbsp\;";

YOu could also have left in the doublequotes but just escaped them
echo "<table border=\"1\">";

[Mtlinfo]

Yeah thanks, all I had to do was add the code <p> at the end of the code </table> so now the space was added between each one.

Thanks for the help man, you may not be a guru but you are one hell of a good guy to me, and super fast too :-)

Mtlinfo

[Mtlinfo]

Btw do you know the exact code to insert a date from a form so that a 14 digit date (Unix_Timestamp) appears in my db?

Right now the plain HTML form inserts the name, email and age of the the person but in the Updated field all I see is 14 zeros instead of the present date.

I tried stuff like this but of course it didn't work:

<input type='hidden' name='Updated' value='timestamp(14)'>

Thanks,

Mtlinfo

[jatar_k]

if you are looking for the present time you can use UNIX_TIMESTAMP() [mysql.com] in your insert query

[slade7]

FWIW - I may not be doing this right, but when escaping a string with tag attributes I use this recipe

echo "<table border=1 width=350 valign=\"top\">";

I use \ to escape quotes for attributes that are non-numeric - the numeric types are work fine for me with no quotes - and if I remember correctly, this will even validate.

for &amp; and &nbsp; I never escape with \ or use quotes.

<td>&nbsp;</td>

or <a href=\"/thisfile.php?this=$this&amp;that=$that\">

[Mtlinfo]

Thanks for your help but I managed to found on the web what I've been doing wrong.

Here's the query that I had before. It was adding data to the db alright but the date in the Updated field wasn't as I wanted it. All I saw was 14 zeros instead of a precise date.

$query = "insert into Ads_Data VALUES ('$Ad_Title', '$Ad_Desc', '$Ad_Sign', '$Updated'";

I found out that I had to separate that line of code into 2 lines, one with the name of the fields ex: 'Ad_Title' and another line below with the values ex: '$Ad_Title'.

By separating the 2 lines as you can see below, I was able to insert the NOW() string with no '' between that specific code and the 14 zeros were replace with the exact time the ad was created.

$query = "insert into Ads_Data (`Ad_Title`, `Ad_Desc`, `Ad_Sign`, `Updated`)
VALUES ('$Ad_Title', '$Ad_Desc', '$Ad_Sign', NOW())";

I came back here and pasted the info just in case someone had troubles on how to insert a date in a MySQL db using an HTML form.

Here's the entire page code now for beginners. Just copy the code and name the page insert.php.

I decided to make the field "$Updated" a hidden one in the form because the date was used only to placed the latest ads on top of the page and not to be seen by people.

<?php

if ($submit == "click"){
// The submit button was clicked!
// Get the input for fullname and email then store it in the database.
$connection = mysql_connect ("localhost", "your_username", "your_password");
if ($connection == false){
echo mysql_errno().": ".mysql_error()."<BR>";
exit;
}

$query = "insert into Ads_Data (`Ad_Title`, `Ad_Desc`, `Ad_Sign`, `Updated`)
VALUES ('$Ad_Title', '$Ad_Desc', '$Ad_Sign', NOW())";
$result = mysql_db_query ("my_db", $query);
if ($result){
echo "Success!";
}
else{
echo mysql_errno().": ".mysql_error()."<BR>";
}

mysql_close ();
}
else{
echo "
<html><body>

<form method=\"post\" action=\"insert.php\">

Title of the ad (Max 70 chrs):
<br>
<input type=\"text\" name=\"Ad_Title\" size=\"50\"></input>
<p>
Description of the ad : (Max 255 chrs):
<br>
<textarea name=\"Ad_Desc\" cols=\"38\" rows=\"2\"></textarea>
<p>
Contact Info : (Max 70 chrs)
<br>
<input type=\"text\" name=\"Ad_Sign\" size=\"50\"></input>
<br>
<input type=\"hidden\" name=\"Updated\" value=\"& date &\"></input><br>

<input type=\"submit\" name=\"submit\" value=\"click\"></input>

</form>

</body></html>
";
}

?>