Some Query Help

RSprinkel · March 21, 2007

Ok I am not sure if this goes here or in the MySQL section, so Admins if it needs to be moved thats no problem.

Anyways here is where I am at.

I have a db table with the following Columns: ID, username, date, series, mod, location. The Mod has 3 different types of info.

On one page I would like to have a Table displaying 3 different types of data from this one table in the db.

First it would query the username as this is for a Profile page then query for the information.

I have this for the Username query:

<?
$sql = "SELECT * FROM win WHERE sierra = '$sierra'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
?>

The query would then have to search for different entries in that same MySQL Table for the Mod and display it in seperate columns.

For instance my Displayed table will look as such.

Column1 Column2 | Column3 Column4 | Column5 Column6

Date Mod1 Date Mod2 Date Mod3

Hope this is clear enough, LOL. I need more coffee I guess.

Thanks much in advance.

btherl · March 21, 2007

The second query will look for other users with the same mod?

$mod = $row['mod']; # From the first query
$sql = "SELECT date, mod FROM win WHERE mod = '$mod'";

It wouldn't be necessary to select mod again since you already know what it is..

RSprinkel · March 21, 2007

Ok not sure I understand but this is what I did and now getting syntax error. So I KNOW I have something wrong, again I am a novice at this stuff, so please bare with me.

<p align="center"> </p>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#C0C0C0" width="75%" id="AutoNumber2">
    <tr>
      <td width="33%" align="center"><font color="#FFFFFF"><b>Rookie Series</b></font></td>
      <td width="33%" align="center"><font color="#FFFFFF"><b>Pro Truck Series</b></font></td>
      <td width="34%" align="center"><font color="#FFFFFF"><b>Pro Cup Series</b></font></td>
    </tr>
  </table>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#C0C0C0" width="75%" id="AutoNumber3">
    <tr>
      <td width="16%" align="center"><font color="#00FFFF"><b>Date</b></font></td>
      <td width="17%" align="center"><font color="#00FFFF"><b>Track</b></font></td>
      <td width="16%" align="center"><font color="#00FFFF"><b>Date</b></font></td>
      <td width="17%" align="center"><font color="#00FFFF"><b>Track</b></font></td>
      <td width="17%" align="center"><font color="#00FFFF"><b>Date</b></font></td>
      <td width="17%" align="center"><font color="#00FFFF"><b>Track</b></font></td>
    </tr>
  </table>
  <table border="1" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#C0C0C0" width="75%" id="AutoNumber3">
    <?
$sql = "SELECT * FROM win WHERE sierra = '$sierra'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
$nmod = $row['Truck']; 
$sql = "SELECT date, nmod FROM win WHERE nmod = '$mod'";
{
?>
    <tr style="color: #111111">
<td align="center"> <font color="#FFFF00"><?php echo $row["date"];?> </font></td>
<td align="center"><font color="#FFFF00"><?php echo $row["track"];?></font></td>
</tr>
  <?
$sql = "SELECT * FROM win WHERE sierra = '$sierra'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
$nmod = $row['Cup']; 
$sql = "SELECT date, nmod FROM win WHERE nmod = '$mod'";
{
?>
      <tr style="color: #111111">
<td align="center"> <font color="#FFFF00"><?php echo $row["date"];?> </font></td>
<td align="center"><font color="#FFFF00"><?php echo $row["track"];?></font></td>
</tr>
	  <?
$sql = "SELECT * FROM win WHERE sierra = '$sierra'";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result))
$nmod = $row['Rookie']; 
$sql = "SELECT date, nmod FROM win WHERE nmod = '$mod'";
{
?>
      <tr style="color: #111111">
<td align="center"> <font color="#FFFF00"><?php echo $row["date"];?> </font></td>
<td align="center"><font color="#FFFF00"><?php echo $row["track"];?></font></td>
</tr>
  </table>

Again thanks for the help, it is much appreciated.

UTAlan · March 21, 2007

Try:

while ($row = mysql_fetch_array($result)) {
$nmod = $row['Truck']; 
$sql = mysql_query("SELECT date, nmod FROM win WHERE nmod = '$nmod'");
?>
<tr style="color: #111111">
	<td align="center"> <font color="#FFFF00"><?php echo $row["date"];?> </font></td>
	<td align="center"><font color="#FFFF00"><?php echo $row["track"];?></font></td>
</tr>
<?
}

And so on for the 3 sections. I think your syntax error is with your brackets not being correct around your while loops. Also, you aren't doing anything with $sql right now. Is that where "date" is supposed to come from?

RSprinkel · March 21, 2007

Ok I got the syntax issue worked out.

UTAlan, Thanks for the help. I guess I left something out when I posted my original message.

We run 3 different series (Rookie, Truck, Cup) and I wanted 2 columns per series each series would have column 1 for date and column 2 for track.

If you look here http://www.cfrlracing.com/VA/showprofile.php?sierra=S_White you will see mid way what I am trying to accomplish. Below what I am trying to accomplish is what I originally had. They both show the same when the one I am trying to do will break them down into each different series.

Again thanks for the help, much appreciated.

UTAlan · March 21, 2007

There's 2 ways that I can see you doing this.

1) Reorganize your tables. Instead of having 1 table for the headers ("Date / Track / Date / Track / Date / Track") and 1 for all of your information, you would have 3 tables, 1 for each mod:

PSEUDO CODE:

<table width="33%">
<?php // Header of mod1 ?>
<tr><td>Date</td><td>Track</td></tr>
<?php // SELECT * FROM win WHERE mod = '$mod' ?>
<tr><td>mysql_result(date)</td><td>mysql_result(track)</td></tr>
</table>

Do that 3 times, having the width such that they will sit side-by-side.

2) Create 3 index variables, 1 for each mod. Do a "SELECT * FROM win". Do something similar to the following:

<?php
$sql = mysql_query("SELECT * FROM win");
$modIndex1 = 0; $modIndex2 = 0; $modIndex3 = 0;
?>
<table>
<?php // Loop through each row (tr) of the table ?>
for($i = 0; $i < mysql_num_rows($sql); $i++) {
     <tr>
     <?php 
     // Get the next index (initially starting at 0) where nmod == Rookie
     while(mysql_result($sql, $modIndex1, 'nmod') != "Rookie" && $modIndex1 < mysql_num_rows($sql)) {
          $modIndex1++;
     }
     // Make sure an index was found before reaching the end of the table
     if($modIndex1 < mysql_num_rows($sql)) {
          // Display information
          echo "<td>mysql_result($sql, $modIndex1, 'date')</td><td>mysql_result($sql, $modIndex1, 'track')</td>\n";
     }
     // Repeat the above While and If statements 2 more times for the other two mods
     ?>
     </tr>
}
</table>

The first option is much more efficient, it's just a matter of getting the table lined up correctly. It's also much neater and cleaner code.

Let me know if you need clarification on any of this.

RSprinkel · March 21, 2007

UTAlan,

Thanks for the reply. Well the first choice seems to be the easiest for me to understand, but I have removed the other table(s) and pasted what you posted now I am not getting anything displayed showing as far as a table.

Now the 2 lines that you have commented out with // I even removed the // and still nothing.

Boy this stuff is hard to grasp, LOL.

Probably be easier for me to create seperate tables in the database and create something else, LOL. Just kidding.

UTAlan · March 21, 2007

If you wanna go with option 1, try this:

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<tr>
<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODONE'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<td>$date</td>\n<td>$track</td>\n";
}
?>
</tr>
</table>

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<tr>
<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODTWO'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<td>$date</td>\n<td>$track</td>\n";
}
?>
</tr>
</table>

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<tr>
<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODTHREE'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<td>$date</td>\n<td>$track</td>\n";
}
?>
</tr>
</table>

Just be sure to change MODONE, MODTWO, and MODTHREE to the appropriate values.

RSprinkel · March 21, 2007

Ok now we are getting somewhere, however it wants to place this in the wrong format. It is showing the data going across instead of down.

Would that be the cause of the <tr?

UTAlan · March 21, 2007

Yes. My fault. Try this instead:

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODONE'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<tr><td>$date</td>\n<td>$track</td></tr>\n";
}
?>
</table>

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODTWO'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<tr><td>$date</td>\n<td>$track</td></tr>\n";
}
?>
</table>

<table width="33%">
<tr>
     <td>Date</td>
     <td>Track</td>
</tr>

<?php
$getMod = mysql_query("SELECT * FROM win WHERE nmod = 'MODTHREE'");
$numRows = mysql_num_rows($getMod);
for($i = 0; $i < $numRows; $i++) {
     $date = mysql_result($getMod, $i, 'date');
     $track = mysql_result($getMod, $i, 'track');
     echo "<tr><td>$date</td>\n<td>$track</td></tr>\n";
}
?>
</table>

UTAlan · March 21, 2007

Also, instead of this:

<table width="33%">

try this:

<table style="position:relative; float:left;" width="33%">

RSprinkel · March 21, 2007

OK, now we have it, THANKS very Much. I am now trying to get some color in there so we can see everything, LOL. Plus center the data.

Again THANKS

RSprinkel · March 21, 2007

Also a Heading for the series, LOL

Got the centering and color done.

RSprinkel · March 21, 2007

Ok not working.

It is not doing the query per member.

If you go here http://www.cfrlracing.com on right hand side of the page you will see meet the drivers if you click on any name listed it shows the same for all and not for the selected driver. It has to query the win for the username $sierra first.

Thanks UTAlan for all your help. Looks good though.

UTAlan · March 21, 2007

I think you should just have to change your queries...

SELECT * FROM win WHERE nmod = 'MODONE' AND username = '$sierra'

Sign In

Some Query Help

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information