First attempt at PHP/MySQL, not displaying data from 1st row in DB, what's wrong?

[edesignuk]

I'm just starting to try and get my head round retrieving data from MySQL with PHP. This is the PHP I am using to get a few bits of data from my database:
<?php
// Connect to MySQL server on localhost
$connection = mysql_connect("localhost","root","catch22")
or die ("Could not connect to MySQL.");
// Select database from MySQL server
$db = mysql_select_db("intranet",$connection)
or die ("Could not select database.");
// Select all data from table
$query = "SELECT * FROM survey";
$result = mysql_query($query)
or die ("Could not select all data from table.");

$row = mysql_fetch_array($result,MYSQL_ASSOC);

while ($row = mysql_fetch_array($result))
{
echo "SurveyID: ".$row['SurveyID']."<br>";
echo "Q1: ".$row['Q1']."<br>";
echo "Q2: ".$row['Q2']."<br>";
echo "Q3: ".$row['Q3']."<br>";
echo "Q4: ".$row['Q4']."<br>";
echo "Q5: ".$row['Q5']."<br>";
echo "Submitted: ".$row['SUBMITTED']."<br>";
}
?>

This is the result I get:SurveyID: 2
Q1: 5
Q2: 3
Q3: 1
Q4: 3
Q5: 4
Submitted: 2009-12-20 04:00:00
SurveyID: 3
Q1: 3
Q2: 2
Q3: 4
Q4: 5
Q5: 3
Submitted: 2009-12-20 04:00:00
SurveyID: 4
Q1: 2
Q2: 2
Q3: 1
Q4: 3
Q5: 1
Submitted: 2009-12-20 04:00:00As you can see, "SurveyID: 1" is missing, this is the first row in the database. Does anyone know why the PHP I have is missing the first row?

Many thanks,

One confused edesignuk :)

btw, if this code is wrong, blame "PHP & MySQL for Dummies" :o

[SilentPanda]

Your first row is getting stuck here:


$row = mysql_fetch_array($result,MYSQL_ASSOC);


Then this $row will actually be your second row.


while ($row = mysql_fetch_array($result))


Don't say I never gave ya anything... :) Unless I'm wrong. :eek:

[edesignuk]

Thanks SilentPanda, but I don't understand what I need to do to fix it :confused:

[SilentPanda]

Get rid of this row:


$row = mysql_fetch_array($result,MYSQL_ASSOC);


Add the MYSQL_ASSOC into your while statement.

Should be good then.


<?php
// Connect to MySQL server on localhost
$connection = mysql_connect("localhost","root","catch22")
or die ("Could not connect to MySQL.");
// Select database from MySQL server
$db = mysql_select_db("intranet",$connection)
or die ("Could not select database.");
// Select all data from table
$query = "SELECT * FROM survey";
$result = mysql_query($query)
or die ("Could not select all data from table.");

// REMOVED A LINE HERE

// ADDED A BIT IN HERE
while ($row = mysql_fetch_array($result,MYSQL_ASSOC))
{
echo "SurveyID: ".$row['SurveyID']."<br>";
echo "Q1: ".$row['Q1']."<br>";
echo "Q2: ".$row['Q2']."<br>";
echo "Q3: ".$row['Q3']."<br>";
echo "Q4: ".$row['Q4']."<br>";
echo "Q5: ".$row['Q5']."<br>";
echo "Submitted: ".$row['SUBMITTED']."<br>";
}
?>

[edesignuk]

PERFECT! Thank you very much! :)

[SilentPanda]

You understand what was happening wrong correct? The first row of your database was being put into $row and then you were putting the second row of your database into $row and then printing things out looping through your database.

$row = whatever // this would store row 1

while ($row = whatever // this would do rows 2 onwards) {
print $row // this will only print rows 2 onwards
}

You woulda been okay if you had used a do... while loop.

[edesignuk]

Yup, I'm with you, another quick question :D

I have a textarea in a form, the content of which I am saving to my DB. How do I maintain new lines when the text is put in to the database?

For example, if there is a text box and the text entered is this:

This is line 1.
This is line 2.
This is line 3.

If saved to the DB with this script:


<?php
$q1answer = $_POST['Q1'];
$q2answer = $_POST['Q2'];
$q3answer = $_POST['Q3'];
$q4answer = $_POST['Q4'];
$q5answer = $_POST['Q5'];
$datetime = $_POST['datetime'];
$comments = $_POST['comments'];

// Connect to MySQL server on localhost
$connection = mysql_connect("localhost","root","catch22")
or die ("Could not connect to MySQL.");
// Select database from MySQL server
$db = mysql_select_db("intranet",$connection)
or die ("Could not select database.");
// SQL command
$query = "INSERT INTO survey
VALUES ('','$q1answer','$q2answer','$q3answer','$q4answer','$q5answer','$datetime','$comments')";
// Execute query
$result = mysql_query($query)
or die ("Could not execute query.");
?>

When displayed it will show as:

This is line 1. This is line 2. This is line 3.

I assume there is some function I need to perform on the text before it's input into the database to keep the line break, but I'm clueless as to what it is?

[edesignuk]

Don't worry! Got it, "nl2br" :)

[Rower_CPU]

Looks like you're making progress, edesign'. :)

[SilentPanda]

He's try to make a competitor against MacRumors! Stop him! :)

[neilrobinson]

which book are you using (if any) to learn? most things i can pickup easy but this has me slightly stumped sometimes usually only with the more advanced stuff tho.
otherwise any recommendations?

[edesignuk]

Looks like you're making progress, edesign'. :)hehe, yes, getting there :)
He's try to make a competitor against MacRumors! Stop him! :)
LOL. This is all from an intranet I've been asked to do for work, the boss wants an online survey etc.
which book are you using (if any) to learn? most things i can pickup easy but this has me slightly stumped sometimes usually only with the more advanced stuff tho.
otherwise any recommendations?btw, if this code is wrong, blame "PHP & MySQL for Dummies" :o

[neilrobinson]

** sheepishly looking around, slinks out of thread before somebody else replies :o

sorry... thanks for being gentle.

[SilentPanda]

LOL. This is all from an intranet I've been asked to do for work, the boss wants an online survey etc.

Suuuuuuure it is... stop trying to hypnotize us with your 'tar... :)