RSprinkel Posted March 21, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
btherl Posted March 21, 2007 Share Posted 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.. Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted 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> Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted March 21, 2007 Also, instead of this: <table width="33%"> try this: <table style="position:relative; float:left;" width="33%"> Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted March 21, 2007 Also a Heading for the series, LOL Got the centering and color done. Quote Link to comment Share on other sites More sharing options...
RSprinkel Posted March 21, 2007 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
UTAlan Posted March 21, 2007 Share Posted March 21, 2007 I think you should just have to change your queries... SELECT * FROM win WHERE nmod = 'MODONE' AND username = '$sierra' Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.