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 Mike Lewis 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
0
0
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 feherke
I have typed the mentioned code and below is the outcome, I think you write it does have a problem with $_POST data.
Any idea how we could fix this?
Regards
HB25
Array ( [firstname] => Alan [surname] => smith [address1] => 75 Paltan
Road [address2] => didsbury [town] => Manchester [postcode] => M20 1JK
[telephone] => 01612233211 => a.smith @smith.com [cardno] =>
123456789410 [expirydate] => 001 [startdate] => 02/04/2009 [enddate] =>
05/04/2009 [roomtype] => Single [adults] => 1 [children] => 0
[requirements] => Pc [booking] => Book a Room ) INSERT INTO bookings
(clientID, roomID, startdate, enddate, adults, children, roomtype,
requirements) VALUES
('50','NULL','2009-04-02','2009-04-05','1','0','Single','Pc')
 
Upvote 0 Downvote
Hi

Ok, then let us do it like this, then post what appears in the web page between the horizontal rules :
Code: 
[small]<?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')";[/small]

[red]echo "<hr><pre>\n";
print_r($_POST);
echo "startdate : '$startdate'
enddate   : '$enddate'
statement : $sql
</pre><hr>";[/red]

[small]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);
?>[/small]

Feherke.
 
Upvote 0 Downvote
Hi feherke

Blow is what was between the horizontal line, but the dates in the table printed is (0000-00-00.

Array
(
[firstname] => Alan
[surname] => smith
[address1] => 75 Paltan Road
[address2] => didsbury
[town] => Manchester
[postcode] => M20 1JK
[telephone] => 01612233211
=> a.smith @smith.com
[cardno] => 123456789410
[expirydate] => 001
[startdate] => 10/04/2009
[enddate] => 15/04/2009
[roomtype] => Single
[adults] => 1
[children] => 0
[requirements] => Pc
[booking] => Book a Room
)
startdate : '10/04/2009'
enddate : '15/04/2009'
statement : INSERT INTO bookings (clientID, roomID, startdate, enddate,
adults, children, roomtype, requirements) VALUES ('51','NULL','10/04
/2009','15/04/2009','1','0','Single','Pc')
----------------------------------------------------------

this was printed in the table

Arrival 0000-00-00
Departure 0000-00-00
 
Upvote 0 Downvote
Hi

Oops, I misunderstood your words :
HB25 said:
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
Click to expand...
I interpreted you words like "I changed the date format and now works".

Well, if you are going forward with slash ( / ) as date separator, then just tell it to MySQL too :
Code: 
$sql="INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('$last_insert_client_id', 'NULL', str_to_date('$startdate','%d[red]/[/red]%m[red]/[/red]%Y'), str_to_date('$enddate','%d[red]/[/red]%m[red]/[/red]%Y'), '$adults', '$children', '$roomtype', '$requirements')";

Feherke.
 
Upvote 0 Downvote
Hi
This is the outcome, and still prints date in this format 0000-00-00.
Arrival 2009-04-21
Departure 2009-05-22
________________________________________
Array
(
[firstname] => Alan
[surname] => smith
[address1] => hhhhhhhhhhhh
[address2] => hhhh
[town] => hh
[postcode] => hhh
[telephone] => hh
=> hh
[cardno] => h
[expirydate] => h
[startdate] => 21/04/2009
[enddate] => 22/05/2009
[roomtype] => Suite
[adults] => 1
[children] => 0
[requirements] =>
[booking] => Book a Room
)
startdate : '21/04/2009'
enddate : '22/05/2009'
statement : INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype, requirements) VALUES ('58', 'NULL', str_to_date('21/04/2009','%d/%m/%Y'), str_to_date('22/05/2009','%d/%m/%Y'), '1', '0', 'Suite', '')
________________________________________
 
Upvote 0 Downvote
hi
this what i have used :

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]));
$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')";
 
Upvote 0 Downvote
Hi

There you are mixing the two solutions ( should be used either the PHP [tt]preg_replace()[/tt] or the MySQL [tt]str_to_date()[/tt] ), but there is still no reason to fail.

I would try to execute the SQL statement directly in the [tt]mysql[/tt] command line tool ( the one you posted at 2 Mar 09 8:05 ) :
Code: 
INSERT INTO bookings (clientID, roomID, startdate, enddate, adults, children, roomtype,  requirements) VALUES ('58', 'NULL', str_to_date('21/04/2009','%d/%m/%Y'), str_to_date('22/05/2009','%d/%m/%Y'), '1', '0', 'Suite', '');

Feherke.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
import CSV file into MySql table in PhpMyAdmin
Replies
0
Views
205
SashaBuilder3
SashaBuilder3
lexer
  • Locked
  • Question
Join two mysql tables for populate datatable
Replies
3
Views
158
spamjim
spamjim
Brianfree
  • Locked
  • Question
Dropdown list data generator
Replies
3
Views
158
Brianfree
Brianfree
ersatz
  • Locked
  • Question
How to send 2 select from mysql database in email like tables
Replies
5
Views
241
ersatz
ersatz
PeDa
  • Locked
  • Question
Unwanted spaces in php HTML mail 1
Replies
4
Views
175
PeDa
PeDa

Part and Inventory Search

Sponsor

Back
Top