MySQL

php/mysql display data in row and column

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

Last »

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#11

Mar 1st, 2009

No there is only one record in my table with Booking ID 33 and the bookingID column in my table is already an auto increment primary key.

Any more suggestions?

Thanks

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#12

Mar 1st, 2009

BTW this is the query which I am using just in case if it does make deference.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='$_POST[bookingID]'");
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='$_POST[bookingID]'");

•

Join Date: Oct 2006

Posts: 206

Reputation: MaxMumford is an unknown quantity at this point

Solved Threads: 2

MaxMumford MaxMumford is offline

Offline

Posting Whiz in Training

Re: php/mysql display data in row and column

#13

Mar 1st, 2009

try using this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");
$result = mysql_query("SELECT * FROM bookings WHERE bookingID='".$_POST['bookingID']."'");

sometimes when you don't encapsulate the second part of the where condition in speech marks it treats it like a search term when your saying "select * from bookings where bookingID like '%term%'

Last edited by MaxMumford; Mar 1st, 2009 at 10:11 am.

Ill solve somebody's thread someday! xD

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#14

Mar 1st, 2009

same thing hapning

•

Join Date: Oct 2006

Posts: 206

Reputation: MaxMumford is an unknown quantity at this point

Solved Threads: 2

MaxMumford MaxMumford is offline

Offline

Posting Whiz in Training

Re: php/mysql display data in row and column

#15

Mar 1st, 2009

Thats really wierd..

Can you send the entire code to me?

Ill solve somebody's thread someday! xD

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#16

Mar 1st, 2009

This is my code

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<?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);
?>
<?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);
?>

Thanks for your help

•

Join Date: Oct 2006

Posts: 206

Reputation: MaxMumford is an unknown quantity at this point

Solved Threads: 2

MaxMumford MaxMumford is offline

Offline

Posting Whiz in Training

Re: php/mysql display data in row and column

#17

Mar 1st, 2009

Ahhh i know whats going wrong. This bit:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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>';
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>';

is saying that for every piece of information saved inside the results array, write a table with the results. Take that out of the foreach function and have it on its own. Here how the whole code should look:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
<?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>';
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);
?>
<?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>';
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);
?>

The query returned 9 pieces of information and stored them inside an array. Then the foreach function was going through all nine items in the array and printing your table of results

Ill solve somebody's thread someday! xD

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#18

Mar 1st, 2009

Hi MaxMumford
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

•

Join Date: Nov 2007

Posts: 3,761

Reputation: nav33n is a jewel in the rough

Solved Threads: 332

nav33n

Offline

Senior Poster

Re: php/mysql display data in row and column

#19

Mar 2nd, 2009

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
<?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']."'");
if(mysql_num_rows($result) > 0 ) { 
 //only print this if there is atleast 1 row. This will avoid a lot of problems which could arise from mysql_fetch_array if there are no rows in the table
//I would also declare the table headers when I am sure at least 1 record exist. If there aren't any records, it wont print the html table 
echo '<table border=1>
<tr><td>Start Date</td><td>End Date</td><td>Adults</td><td>Children</td><td>Roomtype</td><td>Requirements</td></tr>';
$result_array = mysql_fetch_array($result);
 
echo '<td>'.$result_array['startdate'].'</td>';
echo '<td>'.$result_array['enddate'].'</td>';
echo '<td>'.$result_array['adults'].'</td>';
echo '<td>'.$result_array['children'].'</td>';
echo '<td>'.$result_array['roomtype'].'</td>';
echo '<td>'.$result_array['requirements'].'</td>';
echo '</tr>';
echo '</table>'; // this way it looks more neat
}
mysql_close($con);
?>
<?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']."'");
if(mysql_num_rows($result) > 0 ) { 
 //only print this if there is atleast 1 row. This will avoid a lot of problems which could arise from mysql_fetch_array if there are no rows in the table
//I would also declare the table headers when I am sure at least 1 record exist. If there aren't any records, it wont print the html table 
echo '<table border=1>
<tr><td>Start Date</td><td>End Date</td><td>Adults</td><td>Children</td><td>Roomtype</td><td>Requirements</td></tr>';
$result_array = mysql_fetch_array($result);

echo '<td>'.$result_array['startdate'].'</td>';
echo '<td>'.$result_array['enddate'].'</td>';
echo '<td>'.$result_array['adults'].'</td>';
echo '<td>'.$result_array['children'].'</td>';
echo '<td>'.$result_array['roomtype'].'</td>';
echo '<td>'.$result_array['requirements'].'</td>';
echo '</tr>';
echo '</table>'; // this way it looks more neat
}
mysql_close($con);
?>

Also, mysql stores date in the default format yyyy-mm-dd . If you want to change this, you have to specify the column datatype as varchar. I don't recommend it since you can do so many date functions if the column is of date datatype.
Also, php's date function is so vast, you can modify any of your date format to whatever format you want.
Check here.
http://in.php.net/date

Ignorance is definitely not bliss!

*PM asking for help will be ignored*

•

Join Date: Dec 2007

Posts: 74

Reputation: HB25 is an unknown quantity at this point

Solved Threads: 0

HB25

Offline

Junior Poster in Training

Re: php/mysql display data in row and column

#20

Mar 2nd, 2009

Hi
I have gathered from searching the net that I could use the code below to format the date as (DD-MM-YYYY)

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
INSERT INTO table ( FIELD ) VALUES ( STR_TO_DATE($date,'%d-%m-%Y') );
insert into table ( field ) values ( str_to_date($date,'%d-%m-%Y') );

The code below is the query which I am using, could you please let me know how I could embed the above code to this query?

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

$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