mySQL PHP display question

[jamesyg]

Hi there wondering if anyone can help me, I new to mysql and php and in really stuck...

Basically I have created a profile system that you have to register your account details eg username and password. These then go onto a database in MySQL. Once you have registered you then get the opportunity to then complete a profile form. This form has details like name, passport info, frequent flyer info etc.

This will then save into another separate MySQL database. Once the data is entered you then get taken back to the main screen.

The main screen has an option to amend the details you just entered and this is where my problem begins.

I would like to show just the record you have entered into the database but my coding displays all the other records in the database. I cannot figure out how to just display the ‘id’ I have just created.

I have attached my coding below, any help would be really appreciated as I’m beginning to pull out whatever hair I have left!

Thanks

James



include("connect.php");

$query = "SELECT * FROM profile ";
$result = mysql_query($query);
$num = mysql_num_rows ($result);
mysql_close();

if ($num > 0 ) {
$i=0;
while ($i < $num) {
$first_name = mysql_result($result,$i,"first_name");
$surname = mysql_result($result,$i,"surname");
$company_email = mysql_result($result,$i,"company_email");
$mobile_number = mysql_result($result,$i,"mobile_number");
$ba_number = mysql_result($result,$i,"ba_number");
$id = mysql_result($result,$i,"id");

echo "<b>first name:</b> $first_name<br>";
echo "<b>surname:</b> $surname<br>";
echo "<b>company email:</b> $company_email<br>";
echo "<b>mobile number:</b> $mobile_number<br>";
echo "<b>ba number:</b> $ba_number<br>";
echo "<a href=\"update.php?id=$id\">Update</a> - <a href=\"delete.php?id=$id\">Delete</a>";
echo "<br><br>";

++$i; } } else { echo "The database is empty"; }?>

[penders]

$query = "SELECT * FROM profile WHERE id='$id'";

You need to pass the value of 'id' back to this script. You could perhaps do this in the URL...?

<a href="amend-details.php?id=123">Amend Details</a>

[jamesyg]

Thanks for your quick reply, I will try and amend it to the above.
Your suggestion on passing the the 'id' back to the script, do you mean at this part of the script?

echo "<a href=\"update.php?id=$id\">Update</a> - <a href=\"delete.php?id=$id\">Delete</a>";

[penders]

Yes, sorry I missed that, you seem to be doing that already.

In your script you can get the value of the id URL param like so...

$id = isset($_GET['id']) ? $_GET['id'] : null;

if (is_null($id)) { 
  // id has not been passed 
}

[rocknbil]

I would like to show just the record you have entered into the database but my coding displays all the other records in the database.

That problem is here, which selects all records.

$query = "SELECT * FROM profile ";

Without a where statement, it will indeed do that. This looks like a single entry "edit form" so it's really important to validate it's pulling a single record by id.

if (isset($_GET['id']) and is_numeric($_GET['id']) and ($_GET['id'] > 0)) {
$id = $_GET['id'];
}
else { die("There is no ID to get!"); }
$query = "SELECT * FROM profile where id=$id";

$id and the field it's in should be numeric, no reason to quote the value in the mySQL statement.

This stuff right here drives me nuts and I'll never understand why I see it this way so often. :-);

$num = mysql_num_rows ($result);
mysql_close();
if ($num > 0 ) {
$i=0;
while ($i < $num) {

The only real reason to use mysql_num_rows is in the context of multiple records expected, and even then only if you need the number of rows value to pass on to something else, like pagination functions.
if ($num > 0 ) {
// Do it
}
else { echo "No records found with that query"; }

With mysql there is no reason to iterate through a counter.
mysql_result($result,$i,"ba_number")

mysql has a couple row fetching functions.

$num = mysql_num_rows ($result); 
if ($num > 0 ) { 
 while ($row=mysql_fetch_array($result)) { 
 // output the rows HERE 
 } 
} 
else { echo "No records found with that query"; } 

Or, as I said, if there's no use for the $num variable, just do this.

$somecontent=null; 
while ($row=mysql_fetch_array($result)) { 
 $someconent .= $row['somefield']; 
} 
if (! $somecontent) { echo "No records found with that query"; } 
else { echo $somecontent; } 

In your case, you don't even need to count rows. You expect one record. There really is no reason to use "while". If you expect a single record, you can use "if." The output is **usually** the same but when I encounter this in someone's script (I'm fixing) I have to stop and ask, are multiple records expected here? Am I missing something important? Usually the answer is no, but your programming tells a story, using the correct verbs, adjectives, articles, and nouns makes it comprehensible. :-)

Last, you should only output the form if there's data to get - inside the if block. Otherwise your variables are undefined when no records are found which may not seem like it's doing anything, but it is - it's clogging up your error logs with undefined variable warnings. In my example below, you don't even need to use the interim variables.

All together,

include("connect.php"); 
// 
if (isset($_GET['id']) and is_numeric($_GET['id']) and ($_GET['id'] > 0)) { 
$id = $_GET['id']; 
} 
else { die("There is no ID to get!"); } 
// 
$query = "SELECT * FROM profile where id=$id"; 
$result = mysql_query($query) or die("Cannot select user profile"); 
// 
if ($row=mysql_fetch_array($result)) { 
 $myfields = array( 
 'first_name' => 'First Name', 
 'surname' => 'Surname', 
 'company_email' => 'Company Email', 
 'ba_number' => 'BA Number' 
 ); 
 foreach ($myfields as $fieldname => $label) { 
 echo "<p><b>$label:</b>" . $row[$fieldname] . "</p>\n"; 
 } 
 "<p><a href=\"update.php?id=$id\">Update</a> - <a href=\"delete.php?id=$id\">Delete</a></p>"; 
} 
else { echo "No records found with that query"; } 

An alternative would be to use this as an actual ADD or MODIFY form, futher economizing your coding. It also cuts out one step for the user.

include("connect.php"); 
// Previously we only needed this for edit. Now we need it for both. 
$myfields = array( 
 'first_name' => 'First Name', 
 'surname' => 'Surname', 
 'company_email' => 'Company Email', 
 'ba_number' => 'BA Number' 
); 
// 
if (isset($_GET['id']) and is_numeric($_GET['id']) and ($_GET['id'] > 0)) { 
 $id = $_GET['id']; 
 $action = 'update.php'; 
 $button_value = 'Update This Record'; 
 $query = "SELECT * FROM profile where id=$id"; 
 $result = mysql_query($query) or die("Cannot select user profile"); 
 if ($row=mysql_fetch_array($result)) { 
 foreach ($myfields as $fieldname => $label) { 
  $_POST[$fieldname]=$row[$fieldname]; 
 } 
 } 
} 
else {  
 $id=null; 
 $action = 'add.php'; 
 $button_value = 'Add New Record'; 
 // Initialize the $_POST variables so we can populate the 
 // form with empty values and no PHP warnings 
 foreach ($myfields as $fieldname => $label) { 
 $_POST[$fieldname]=null; 
 } 
// 
echo "<form action=\"$action\" method=\"post\">\n"; 
if ($id) { echo "<input type=\"hidden\" name=\"id\" value=\"$id\">\n"; } 
// 
foreach ($myfields as $fieldname => $label) { 
 echo "<p><label for=\"$fieldname\">$label:</label> 
 <input type=\"text\" size=\"25\" name=\"$fieldname\" id=\"$fieldname\" value=\"" . $_POST[$fieldname] . "\"></p>\n"; 
} 
echo "<p><input type=\"submit\" value=\"$button_value\"></p>\n"; 
if ($id) { 
 echo "<p><a href=\"delete.php?id=$id\">Delete</a></p>"; 
} 
echo "</form>"; 

None of the above is tested code but I don't see any errors - may need debugging . . .