Having trouble pulling data from MySql db using PHP table.

[FoxRocks]

Hello,

 

I'm very hesitant to post this as I just know it's sure to be an easy fix, but after searching and troubleshooting for most of the afternoon, I just can't stand it anymore.

I've created an html form that posts to a MySql db. That part is working great. The problem is trying to get the record back out of the db to display on my html/php table.

 

Here is the code for the file the form gets posted to (aircraftupdate.php)

 

<?php
$OEQ_Sched = $_POST['OEQ_Sched'];
$OEQ_EMMA = $_POST['OEQ_EMMA'];

$con = mysql_connect ("localhost","user","pass");
if (!$con)
  {
  die('Could not connect: ' .mysql_error());
  }
mysql_select_db("fox_aircraft_status",$con);

mysql_query("UPDATE Aircraft_Daily_Status SET Schedule='.$OEQ_Sched.',EMMA='.$OEQ_EMMA.' WHERE Aircraft='8Q-OEQ'");

mysql_close($con);

header( 'Location: http://www.andrewfox.ca/MAT/index2.php' ) ;
?>

 

Then that redirects to this page where I want to display the record:

 

<div id="aircraft_status">

<a name="aircraft_status">

<table cellpadding="0" cellspacing="0" border="2" class="font18black">

<tr align="center" style="background:#FFF; font-weight:bold;" height="22">

<td>
A/C REG</td>

<td>
SCHEDULE</td>

<td>
EMMA</td>
</tr>

<tr>
<td id="cell1display">8Q-OEQ</td>
<?php
$con = mysql_connect("localhost","fox_mat","10845");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
mysql_select_db("fox_aircraft_status", $con);
mysql_query("SELECT * FROM Aircraft_Daily_Status");

echo"<td id="cell2display">".$OEQ_Sched."</td>";
echo"<td id="cell3display">".$OEQ_EMMA."</td>";

mysql_close($con);
?>
</tr>
</table>

 

With the code shown above I was getting an "unexpected T_STRING, expecting ',' or ';' " error. So I removed the id's from the two cells and that error went away. Trouble is the record didn't display either. Most of my information has come W3 Schools.

 

Thank you for any help

[silkfire]

Mate you must've missed the basics of performing a fetch query in PHP.

 

This:

 

mysql_query("SELECT * FROM Aircraft_Daily_Status");

 

will give you nothing because you haven't assigned the result of that query to a variable.

 

Do like this instead:

 

$query = mysql_query('SELECT `Schedule`, `EMMA`
                      FROM `Aircraft_Daily_Status`
                      WHERE `Aircraft`= \'8Q-OEQ\'');

list($OEQ_Sched, $OEQ_EMMA) = mysql_fetch_row($query);
?>

<td id="cell2display"><?=$OEQ_Sched?></td>
<td id="cell3display"><?=$OEQ_EMMA?></td>

[FoxRocks]

Silkfire...you have no idea how happy you've made me! I just did a "WooHoo" out loud in my office

 

Unfortunately for me I always decide to learn by trial and error, so I chalk this up to a very good lesson learned!!

 

I do have a follow up question as well as a request if you don't mind.

 

My request is, can you walk me through the "list" section of that code? I understand everything else, and that part sort of makes sense, but I want to make sure I "get it".

 

My follow up question is, as you can probably tell this is for some of the company aircraft, of which we have 21 machines. How would I go about producing this same result for each aircraft in the database? If I were to guess at it I would probably say that I would have to name each query result variable a different name and then repeat it for each aircraft. The reason I doubt that is the best way is...well...because I can image how much code would be involved for this little project, never mind one that is much bigger.

 

You kick ass, thank you so much for helping me out, I'm grinning ear to ear right now!

 

~FOX~

[FoxRocks]

Ok, I was so excited I forgot about a second question I have.

 

I want to change the background color of the <tr> depending on the overall status of the airplane. I sort of got it to work on the form side with some javascript to change the colors, but I'm totally clueless when it comes to storing a <tr> "state" into a db and then retrieving that "state" on the display table side.

 

I realize this is probably best to start a new thread on, but I just thought I would ask in case it was something easy (for someone who knows what they're doing) to do.

 

Thanks again

[HDFilmMaker2112]

Silkfire...you have no idea how happy you've made me! I just did a "WooHoo" out loud in my office

 

Unfortunately for me I always decide to learn by trial and error, so I chalk this up to a very good lesson learned!!

 

I do have a follow up question as well as a request if you don't mind.

 

My request is, can you walk me through the "list" section of that code? I understand everything else, and that part sort of makes sense, but I want to make sure I "get it".

 

My follow up question is, as you can probably tell this is for some of the company aircraft, of which we have 21 machines. How would I go about producing this same result for each aircraft in the database? If I were to guess at it I would probably say that I would have to name each query result variable a different name and then repeat it for each aircraft. The reason I doubt that is the best way is...well...because I can image how much code would be involved for this little project, never mind one that is much bigger.

 

You kick ass, thank you so much for helping me out, I'm grinning ear to ear right now!

 

~FOX~

 

Look into the while loop.

http://us2.php.net/manual/en/control-structures.while.php

 

 

You can look at the examples on this page to see it use with DB query.

http://us2.php.net/manual/en/function.mysql-fetch-assoc.php

[ManiacDan]

Change your username and password immediately, they've already been seen by potentially hundreds of people, and 4+ web crawlers.

[FoxRocks]

HDFilmMaker: Thanks for the links, I will check those out.

 

Moderator: I will do that immediately, I copied and pasted in haste. Thanks a lot for editing my post, I'll be more careful next time. 

[litebearer]

rough way to list them all...

 

$query = "SELECT `Schedule`, `EMMA`,  `Aircraft`  FROM `Aircraft_Daily_Status` ";
$result = mysql_query($query);
echo "<table>";
while($row = mysql_fetch_array($result)) {
?>
<tr><td><?php echo $row['Aircraft']; ?></td><td id="cell2display"><?php echo $row['Schedule']; ?></td><td id="cell3display"><?php echo $row['EMMA']; ?></td></tr>
<?php
}
echo "</table>";

[FoxRocks]

Hi Litebearer,

 

Thank you for your interest in my challenge.

I didn't mention this before as I didn't think it was relevant, but my db is never expanding in that the records are always being updated and my php table (my display table) is a of a fixed size with all the rows predetermined...meaning It will always have 21 rows with each row being dedicated to one particular aircraft.

With this in mind, does your layout model support that structure? I only ask because I am so very new to php that I don't want to confuse myself trying to implement something that will never work.

Regardless I still appreciate your help and your time

 

Cheers,

~FOX~

[litebearer]

It will display however many rows you have in your db.

[FoxRocks]

Hi litebearer,

 

I wanted to drop in a quick post just so you I'm not one of those people who ask for help never to be heard from again.

 

I am working on this project with little time remaining until the deadline. Because of this I have done everything (I'm sure) the hard way for now just so it works, but I do plan to clean everything up once I meet the objective. Thanks again for your help, I'll post back when this becomes a priority again.

 

Cheers,

 

~FOX~