Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.

edesignuk

Moderator emeritus
Original poster
Mar 25, 2002
19,232
2
London, England
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:
<?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:00
As 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" :eek:
 
Your first row is getting stuck here:

Code:
$row = mysql_fetch_array($result,MYSQL_ASSOC);

Then this $row will actually be your second row.

Code:
while ($row = mysql_fetch_array($result))

Don't say I never gave ya anything... :) Unless I'm wrong. :eek:
 
Get rid of this row:

Code:
$row = mysql_fetch_array($result,MYSQL_ASSOC);

Add the MYSQL_ASSOC into your while statement.

Should be good then.

PHP:
<?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>";
}
?>
 
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.
 
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:
<?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?
 
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?
 
Rower_CPU said:
Looks like you're making progress, edesign'. :)
hehe, yes, getting there :)
SilentPanda said:
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.
neilrobinson said:
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 said:
btw, if this code is wrong, blame "PHP & MySQL for Dummies" :eek:
 
Register on MacRumors! This sidebar will go away, and you'll see fewer ads.