Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

php/mysql display data in row and column 1

Status
Not open for further replies.
hb25

hb25

Programmer
Mar 1, 2009
21
GB
Hi
I am using the fallowing code to give some information back to the user after running some queries. The information will be displayed in row only.

Code: 
<?php
// open database connection code and then my code as follows


$sql="INSERT INTO clients (firstname, surname, address1, address2, town,  postcode,  telephone, email, cardno, expirydate) VALUES ('$_POST[firstname]','$_POST[surname]','$_POST[address1]',
'$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]',
'$_POST[expirydate]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_client_id = mysql_insert_id();


$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]',
'$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  
  $last_insert_booking_id = mysql_insert_id();
  
 
	$result = mysql_query("SELECT * FROM clients WHERE clientID='$last_insert_client_id'");
	
  $result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");

print "Thank you, your booking is complete, and your booking ID number is  ".$last_insert_booking_id;
  
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Firstname</th>
<th>Lastname</th>
<th>address1</th>
<th>address2</th>
<th>town</th>
<th>postcode</th>
<th>telephone</th>
<th>email</th>
<th>Arrival</th>
<th>Departure</th>
<th>Adults</th>
<th>children</th>
<th>Room Type</th>
<th>Requirements</th>

</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['clientID'] . "</td>";
  echo "<td>" . $row['firstname'] . "</td>";
  echo "<td>" . $row['surname'] . "</td>";
 echo "<td>" .$row['address1'] . "</td>";
echo "<td>" .$row['address2'] . "</td>";
echo "<td>" .$row['town'] . "</td>";
echo "<td>" .$row['postcode'] . "</td>";
echo "<td>" .$row['telephone'] . "</td>";
echo "<td>" .$row['email'] . "</td>";
  } 
  //considering these are the values returned by 1st query (ie., table client)
  while($row = mysql_fetch_array($result1)) {
echo "<td>" .$row['startdate'] . "</td>";
echo "<td>" .$row['enddate'] . "</td>";
echo "<td>" .$row['adults'] . "</td>";
echo "<td>" .$row['children'] . "</td>";
echo "<td>" .$row['roomtype'] . "</td>";
echo "<td>" .$row['requirements'] . "</td>";

  echo "</tr>";
  }
echo "</table>";


mysql_close($con);
?>

I would like to display the information in this way i.e:

-----------------------------------------
First name | Smith
------------------------------------
last Nmae | Alan
------------------------------------
Arrival date| 28/02/2009
------------------------------------
and so on ......

Any suggestion as I am new to PHP. I have attached the image to show how the information is displayed.
Regards
HB25
 
Sort by date Sort by votes
Hi

Like this ?
Code: 
$row = mysql_fetch_array($result);
$row1 = mysql_fetch_array($result1);

echo "<table border='1'>
<tr><th>ID</th><td>" . $row['clientID'] . "</td></tr>
<tr><th>Firstname</th><td>" . $row['firstname'] . "</td></tr>
<tr><th>Lastname</th><td>" . $row['surname'] . "</td></tr>
<tr><th>address1</th><td>" .$row['address1'] . "</td></tr>
<tr><th>address2</th><td>" .$row['address2'] . "</td></tr>
<tr><th>town</th><td>" .$row['town'] . "</td></tr>
<tr><th>postcode</th><td>" .$row['postcode'] . "</td></tr>
<tr><th>telephone</th><td>" .$row['telephone'] . "</td></tr>
<tr><th>email</th><td>" .$row['email'] . "</td></tr>
<tr><th>Arrival</th><td>" .$row1['startdate'] . "</td></tr>
<tr><th>Departure</th><td>" .$row1['enddate'] . "</td></tr>
<tr><th>Adults</th><td>" .$row1['adults'] . "</td></tr>
<tr><th>children</th><td>" .$row1['children'] . "</td></tr>
<tr><th>Room Type</th><td>" .$row1['roomtype'] . "</td></tr>
<tr><th>Requirements</th><td>" .$row1['requirements'] . "</td></tr>
</table>";
( Just typed, not tested. )

Feherke.
 
Upvote 0 Downvote
I have tryed your code it only display one rows and no columns.
 
Upvote 0 Downvote
I have tryed your code it only display rows and no columns.

 
Upvote 0 Downvote
Sorry
this what i get

-----------------------------------------
First name | nothing here (I mean this is empty)
------------------------------------
last Nmae | nothing here (I mean this is empty)
------------------------------------
Arrival date|nothing here (I mean this is empty)
------------------------------------
.
.
. and so on
 
Upvote 0 Downvote
Hi

And how your modified code looks like ? Is it like this ?
Code: 
<?php
// open database connection code and then my code as follows


$sql="INSERT INTO clients (firstname, surname, address1, address2, town,  postcode,  telephone, email, cardno, expirydate) VALUES ('$_POST[firstname]','$_POST[surname]','$_POST[address1]',
'$_POST[address2]','$_POST[town]', '$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]',
'$_POST[expirydate]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_client_id = mysql_insert_id();


$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]',
'$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  
  $last_insert_booking_id = mysql_insert_id();
  
 
    $result = mysql_query("SELECT * FROM clients WHERE clientID='$last_insert_client_id'");
    
  $result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");

print "Thank you, your booking is complete, and your booking ID number is  ".$last_insert_booking_id;
  
$row = mysql_fetch_array($result);
$row1 = mysql_fetch_array($result1);

echo "<table border='1'>
<tr><th>ID</th><td>" . $row['clientID'] . "</td></tr>
<tr><th>Firstname</th><td>" . $row['firstname'] . "</td></tr>
<tr><th>Lastname</th><td>" . $row['surname'] . "</td></tr>
<tr><th>address1</th><td>" .$row['address1'] . "</td></tr>
<tr><th>address2</th><td>" .$row['address2'] . "</td></tr>
<tr><th>town</th><td>" .$row['town'] . "</td></tr>
<tr><th>postcode</th><td>" .$row['postcode'] . "</td></tr>
<tr><th>telephone</th><td>" .$row['telephone'] . "</td></tr>
<tr><th>email</th><td>" .$row['email'] . "</td></tr>
<tr><th>Arrival</th><td>" .$row1['startdate'] . "</td></tr>
<tr><th>Departure</th><td>" .$row1['enddate'] . "</td></tr>
<tr><th>Adults</th><td>" .$row1['adults'] . "</td></tr>
<tr><th>children</th><td>" .$row1['children'] . "</td></tr>
<tr><th>Room Type</th><td>" .$row1['roomtype'] . "</td></tr>
<tr><th>Requirements</th><td>" .$row1['requirements'] . "</td></tr>
</table>";

mysql_close($con);
?>

Feherke.
 
Upvote 0 Downvote
yes

I ma using this code on another page but it repet the table mor than 10 times any idea why is this:

Code: 
<?php

// open database connection code and then my code as follows

$sql="UPDATE bookings SET startdate ='$_POST[startdate]',enddate='$_POST[enddate]',adults='$_POST[adults]',
children='$_POST[children]',roomtype='$_POST[roomtype]', requirements='$_POST[requirements]'
WHERE bookingID = '$_POST[bookingID]'";
 
if (!mysql_query($sql,$con))
  {
  die('Error:  ' . mysql_error());
  } 

  print "Your booking ID ".$bookingID;
  
  echo "    has been changed";


$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");


$result_array = mysql_fetch_array($result);

echo '<table border=1>';

foreach($result_array as $key => $value)
{
echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
}
echo '</table>';


mysql_close($con);
?>
 
Upvote 0 Downvote
Hi

Because you are looping over the fields of the result set.

Just remove the [tt]foreach[/tt] :
Code: 
echo '<table border=1>';
echo '<tr><td>Start Date</td><td>'.$result_array['startdate'].'</td></tr>';
echo '<tr><td>End Date</td><td>'.$result_array['enddate'].'</td></tr>';
echo '<tr><td>Adults</td><td>'.$result_array['adults'].'</td></tr>';
echo '<tr><td>Children</td><td>'.$result_array['children'].'</td></tr>';
echo '<tr><td>Roomtype</td><td>'.$result_array['roomtype'].'</td></tr>';
echo '<tr><td>Requirements</td><td>'.$result_array['requirements'].'</td></tr>';
echo '</table>';

Feherke.
 
Upvote 0 Downvote
Hi feherke
This did work; I just wanted to say a big thank you for solving my thread.

My last problem is at the moment user will have to type date in this format (YYYY-MM-DD) as my phpMyadmin will only store date in this format any idea how I could make it to store date in this format (DD-MM-YYYY)

Once again thanks ever so much for your help.

Regards
HB25
 
Upvote 0 Downvote
Hi

You can either reformat it with PHP :
Code: 
$date4db=preg_replace('/(\d{2})-(\d{2})-(\d{4})/','$3-$2-$1',$date);
Or just tell MySQL about the format :
Code: 
insert into [green][i]table[/i][/green] ( [green][i]field[/i][/green] ) values ( [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_str-to-date]str_to_date[/url]($date,'%d-%m-%Y') );

Feherke.
 
Upvote 0 Downvote
Hi
The code below is the query which I am using, could you please let me know how I could embed the code you are mention above to this query?
Code: 
$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$_POST[startdate]','$_POST[enddate]',
'$_POST[adults]','$_POST[children]','$_POST[roomtype]', '$_POST[requirements]')";
Thanks for your help.
HB25
 
Upvote 0 Downvote
Hi

Unless I made a typo, this should work :
Code: 
$startdate=[url=http://php.net/mysql_real_escape_string/]mysql_real_escape_string[/url](preg_replace('/(\d{2})-(\d{2})-(\d{4})/','$3-$2-$1',$_POST[startdate]));
$enddate=mysql_real_escape_string(preg_replace('/(\d{2})-(\d{2})-(\d{4})/','$3-$2-$1',$_POST[enddate]));
$adults=mysql_real_escape_string($_POST[adults]);
$children=mysql_real_escape_string($_POST[children]);
$roomtype=mysql_real_escape_string($_POST[roomtype]);
$requirements=mysql_real_escape_string($_POST[requirements]);

$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL','$startdate','$enddate','$adults',
'$children','$roomtype','$requirements')";

[gray]// or[/gray]

$startdate=mysql_real_escape_string]($_POST[startdate]);
$enddate=mysql_real_escape_string($_POST[enddate]);
$adults=mysql_real_escape_string($_POST[adults]);
$children=mysql_real_escape_string($_POST[children]);
$roomtype=mysql_real_escape_string($_POST[roomtype]);
$requirements=mysql_real_escape_string($_POST[requirements]);

$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id','NULL',str_to_date('$startdate','%d-%m-%Y'),str_to_date('$enddate','%d-%m-%Y'),'$adults','$children','$roomtype','$requirements')";
Note the most important : never trust the data received from client side. Use the available escaping functions.


Feherke.
 
Upvote 0 Downvote
Hi
The first code is just show (0000-00-00) in the start and end date field, and the second code it say
Error: Column 'startdate' cannot be null
Any more advice?
Thanks
HB25
 
Upvote 0 Downvote
Hi

HB25 said:
The first code is just show (0000-00-00) in the start and end date field
Click to expand...
And how is your $_POST data look like ?
HB25 said:
the second code it say
Error: Column 'startdate' cannot be null
Click to expand...
Oops. Is a typo there. An extra closing bracket ( ] ) :
Code: 
$startdate=mysql_real_escape_string($_POST[startdate]);

Feherke.
 
Upvote 0 Downvote
Hi
1. how is your $_POST data look like ?
02/04/2009 my mistake i have used (/) insted of (-), after change the date to 02-04-2009 it will jsut show the date as 2009-04-02
2. An extra closing bracket ( ] ) :
Again after deleting the extra bracket, i do get the same error message:
Error: Column 'startdate' cannot be null

Any more advice?
Thanks
HB25
 
Upvote 0 Downvote
Hi

With the first solution is easy to allow various separators. Why not accept the date with either slash ( / ), dot ( . ) or dash ( - ) as separator ?
Code: 
$startdate=mysql_real_escape_string(preg_replace(',(\d{2})[/.-](\d{2})
[/.-](\d{4}),','$3-$2-$1',$_POST[startdate]));
$enddate=mysql_real_escape_string(preg_replace(',(\d{2})[/.-](\d{2})
[/.-](\d{4}),','$3-$2-$1',$_POST[enddate]));
No idea about the second solution's error. It works for me. You are not combining the two solutions, right ?

Probably only seeing your code will help.

Feherke.
 
Upvote 0 Downvote
Hi
This is my code :

Code: 
<?php

// open database connection code and then my code as follows


$sql="INSERT INTO clients (firstname, surname, address1, address2,
 town,  postcode,  telephone, email, cardno, expirydate) VALUES 
('$_POST[firstname]','$_POST[surname]','$_POST[address1]',
'$_POST[address2]','$_POST[town]', 
'$_POST[postcode]','$_POST[telephone]','$_POST[email]','$_POST[cardno]',
'$_POST[expirydate]')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
$last_insert_client_id = mysql_insert_id();


$startdate=mysql_real_escape_string(preg_replace('/(\d{2})-(\d{2})-(\d{4})/','$3-$2-$1',$_POST[startdate]));
$enddate=mysql_real_escape_string(preg_replace('/(\d{2})-(\d{2})-(\d{4})/','$3-$2-$1',$_POST[enddate]));
$adults=mysql_real_escape_string($_POST[adults]);
$children=mysql_real_escape_string($_POST[children]);
$roomtype=mysql_real_escape_string($_POST[roomtype]);
$requirements=mysql_real_escape_string($_POST[requirements]);

$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, 
children, roomtype,  requirements) VALUES 
('$last_insert_client_id','NULL','$startdate','$enddate','$adults',
'$children','$roomtype','$requirements')";

if (!mysql_query($sql,$con))
  {
  die('Error: ' . mysql_error());
  }
  
  $last_insert_booking_id = mysql_insert_id();
  
 
    $result = mysql_query("SELECT * FROM clients WHERE clientID='$last_insert_client_id'");
    
  $result1 = mysql_query("SELECT * FROM bookings WHERE bookingID='$last_insert_booking_id'");

print "Thank you, your booking is complete, and your booking ID number is  ".$last_insert_booking_id;
  
$row = mysql_fetch_array($result);
$row1 = mysql_fetch_array($result1);

echo "<table border='1'>
<tr><th>ID</th><td>" . $row['clientID'] . "</td></tr>
<tr><th>Firstname</th><td>" . $row['firstname'] . "</td></tr>
<tr><th>Lastname</th><td>" . $row['surname'] . "</td></tr>
<tr><th>address1</th><td>" .$row['address1'] . "</td></tr>
<tr><th>address2</th><td>" .$row['address2'] . "</td></tr>
<tr><th>town</th><td>" .$row['town'] . "</td></tr>
<tr><th>postcode</th><td>" .$row['postcode'] . "</td></tr>
<tr><th>telephone</th><td>" .$row['telephone'] . "</td></tr>
<tr><th>email</th><td>" .$row['email'] . "</td></tr>
<tr><th>Arrival</th><td>" .$row1['startdate'] . "</td></tr>
<tr><th>Departure</th><td>" .$row1['enddate'] . "</td></tr>
<tr><th>Adults</th><td>" .$row1['adults'] . "</td></tr>
<tr><th>children</th><td>" .$row1['children'] . "</td></tr>
<tr><th>Room Type</th><td>" .$row1['roomtype'] . "</td></tr>
<tr><th>Requirements</th><td>" .$row1['requirements'] . "</td></tr>
</table>";

mysql_close($con);
?>
 
Upvote 0 Downvote
Hi

Well, that is correct and works for me. I would say, the problem is in the $_POST data. For debugging I would add this after the $sql's value is assigned :
Code: 
print_r($_POST);
echo $sql;

Feherke.
 
Upvote 0 Downvote
Hi
Sorry y feherke I am new to PHP and Mysql, could you please apply it to my code and post the code here again.

Thanks ever so much for your kind help.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

W
  • Question
how to send checked row data in php
Replies
0
Views
62
wiltang2004
W
R
  • Question
How Can I Use MySQL To Send Data To An Email
Replies
1
Views
277
Chris Miller
Chris Miller
cdlvj
  • Question
PHP Debugging
Replies
0
Views
208
cdlvj
cdlvj
SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
393
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
272
spamjim
spamjim

Part and Inventory Search

Sponsor

Back
Top