Agreaves Posted August 17, 2012 Share Posted August 17, 2012 Can anyone help me with coding on how to display my database data in a table on the fly. When I say on the fly I mean have the code create a row or column for each row of data thats in a particular table. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/ Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 You'll need to use a while loop and probably some for loops. It's pretty simple, where are you stuck? What have you done so far? Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370294 Share on other sites More sharing options...
Agreaves Posted August 17, 2012 Author Share Posted August 17, 2012 This is the body of webpage, so far I have used the echo function to display a blank table, I have some coding stacked away for displaying the data from my database, so all I need to do is structure the coding to create 4 columns of data for each row. Bare in mind im new to php, I read from 3 different books to help me with my coding. <body> <table width="1024" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td valign="baseline" id="heading"><?php echo products ?></td> <td width="151" align="right" valign="bottom"><table width="150" border="0" cellspacing="0" cellpadding="0"> <tr> <td height="35" align="center"><a href="../index.php" id="btn"><span>Home</span></a></td> </tr> </table></td> </tr> <tr> <td height="40" colspan="2" valign="middle" class="bdr"> </td> </tr> </table><?php echo "<table width='1024' border='0' align='center' cellpadding='25' cellspacing='0' class='main_bdr'>"; echo "</table>"?> <table width="1024" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td height="40" align="center" class="bdr">© <?php echo date("Y");?> MizBones </td> </tr> </table> </body> Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370297 Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 Do you know how to select the data from the database? Once you do that, just loop through it, creating rows... Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370298 Share on other sites More sharing options...
jcbones Posted August 17, 2012 Share Posted August 17, 2012 Sometimes a picture is worth a thousand words. So, I dug way back in the library, and dusted off a very old function that I had. It was tucked way back there, in amongst some crumpled paper. <?php //include connection info. include('config.php'); //point to file that holds database connection info. //function to show all results from a table. function browseData($tbl) { //declare function, argument says to pass the function the table name. $result = mysql_query("SELECT * FROM `$tbl`"); //get the resource. $output = NULL; //start the output variable. $output .= mysql_num_rows($result) . " Results!"; //show rows returned. $c = mysql_num_fields($result); //get a count of the fields. $output .= "<table class=\"search\">"; //start a table. for ($i=0; $i < $c; $i++) { //for( start i at 0, if i is less than fields count then continue, incrementing i as it continues) $output .= '<th style="background-color:blue;">'.ucwords(mysql_field_name($result, $i)).'</th>'; //print a table header for each field name. } $d = 1; //yep, I made useless variables too, long ago. Ah, closer look it is for the background alteration of colors. while($r = mysql_fetch_array($result)) { //fetch the array, should either be row, or assoc (in this case row). $output .= '<tr style="background-color:'; //new table row for each row of data. $output .= (($d % 2) == 0) ? '#ACCDE2;' : '#0088E2;' ; //alternate background color for each row. $output .= '">'; //closing the tr element. $i = 0; //start a new $i variable. while($i < $c) { //while i is less than c (field count). $output .= "<td>" . $r[$i] . "</td>"; //print a td element with the contents of the data row/column. $i++; //increment i } $output .= "</tr>"; //close row. $d++; //increment d (who knows). } $output .= "</table><br/><br/>"; //close table, adding spacing. return $output; //return the function contents. } //call the function, and print the results to the page. $db_table = 'test'; //Your table name. echo browseData($db_table); //echoing a function will print the return of that function. (in this case $output) ?> I commented this function, and even left in those things that I used to do that are not the best coding practices. Although, I told how to fix them in the comments. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370302 Share on other sites More sharing options...
Agreaves Posted August 17, 2012 Author Share Posted August 17, 2012 Yes I have my database connection info in a seperate file. Below is the coding I use to connect to my database, what it does is, whenever a user clicks a hyperlink for a category of products they want to see it sends a query to database and stores the data temporarily in the variable $rows. /* Include database connection file */ include("db_con.inc"); /* Connecting to the database */ $con = mysqli_connect($host,$user,$pass,$dbase) or die("couldnt connect to server"); /* Store the variable for the category obtained from the hyperlink*/ $cat = strtolower($_GET['cat']); /* Select the desired category from the products table */ $query = "SELECT * FROM products WHERE Cat = '$cat'"; /* Run sql query to check the table for the particular category */ $result = mysqli_query($con,$query) or trigger_error ('Could not run select query', E_USER_ERROR); /* Store results to be used */ $rows = mysqli_fetch_assoc($result); Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370304 Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 That last line does not do what you think it does. That's the part you'll loop through. I suggest referencing the manual, or reading the function above. /* Store results to be used */ $rows = mysqli_fetch_assoc($result); Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370306 Share on other sites More sharing options...
Agreaves Posted August 17, 2012 Author Share Posted August 17, 2012 whats does it do?, thats what I use to extract the data from to display it to the webpage. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370309 Share on other sites More sharing options...
KevinM1 Posted August 17, 2012 Share Posted August 17, 2012 Just a slight digression: Don't use all capital letters for a subject title. It reads as you screaming/yelling at people. I've changed it to a more polite normal case title. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370311 Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 It gets one row. You've called it $rows, indicating you think it gets all the rows. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370313 Share on other sites More sharing options...
Agreaves Posted August 17, 2012 Author Share Posted August 17, 2012 Oh you're right how should I structure it to get all the rows of the particular category Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370316 Share on other sites More sharing options...
Jessica Posted August 17, 2012 Share Posted August 17, 2012 You loop through the rows. Look in the manual at the function you're using, there are lots of examples. mysqli_fetch_assoc Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370318 Share on other sites More sharing options...
Agreaves Posted August 18, 2012 Author Share Posted August 18, 2012 Ok so I figured out how to loop the collected data to get all rows but I still need to figure out how to extract the data separately in columns and rows Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370407 Share on other sites More sharing options...
Agreaves Posted August 18, 2012 Author Share Posted August 18, 2012 My database table has 4 columns Id, Name, Price and Pix and I need to extract the Id, Name and Picture of a particular category of product in 4 columns in each row. I need help with the structure of my code to achieve this. Below is where the structure of my code so far, which displays the products in 1 column with each product in a different row. But as I said before I want it to be displayed 4 products per row in the table. echo "<table width='1024' border='0' align='center' cellpadding='25' cellspacing='0' class='main_bdr'>"; echo "<tr>\n <td width='150' align='center'><img src='{$rows['Pix']}' width='150' height='150' /></td>\n <td width='20' id='id'>{$rows ['Id']}</td>\n <td width='431' colspan='-1' rowspan='4'> </td>\n <td width='261' rowspan='4' align='center'> </td>\n </tr>\n"; } echo "</table>"; Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370535 Share on other sites More sharing options...
Jessica Posted August 18, 2012 Share Posted August 18, 2012 This is a good time to use the % operator. echo '<tr>'; $count = 1; while($row=...){ if($count%4==0){ echo '</tr><tr>'; } echo '<td>Product data</td>'; $count++; } echo '</tr>'; Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370551 Share on other sites More sharing options...
Agreaves Posted August 18, 2012 Author Share Posted August 18, 2012 You have to explain to me what that code means, in the mean time ill read up on that oeprator you just mentioned Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370554 Share on other sites More sharing options...
Jessica Posted August 18, 2012 Share Posted August 18, 2012 No, I don't have to. You have to read it and attempt to learn. If you have a specific question ask it. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370556 Share on other sites More sharing options...
Agreaves Posted August 18, 2012 Author Share Posted August 18, 2012 OK fare enough, youre right Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370561 Share on other sites More sharing options...
Agreaves Posted August 19, 2012 Author Share Posted August 19, 2012 I did some adjustment to the code below but it keeps displaying a different image 4 times in each row. What am I not doing or should be doing? /* Create a table to hold the data */ echo "<table width='1024' border='0' align='center' cellpadding='25' cellspacing='0' class='main_bdr'>"; /*Run a loop to retrieve all rows from the database */ while ($rows = mysqli_fetch_assoc($result)) { /* Extract all rows of data from the result */ extract($rows); echo "<tr>"; echo "<td width='150' align='center'><img src='{$rows['Pix']}' width='150' height='150' /></td> <td width='150' align='center'><img src='{$rows['Pix']}' width='150' height='150' /></td> <td width='150' align='center'><img src='{$rows['Pix']}' width='150' height='150' /></td> <td width='150' align='center'><img src='{$rows['Pix']}' width='150' height='150' /></td>"; echo "</tr>"; } echo "</table>"; Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370569 Share on other sites More sharing options...
jazzman1 Posted August 19, 2012 Share Posted August 19, 2012 Why did you loop the same row 4 times ? Put this piece of code immediately after extract($rows); echo '<pre>'.print_r($rows, true).'</pre>'; exit; Post the result, please. /* Extract all rows of data from the result */ extract($rows); echo '<pre>'.print_r($rows, true).'</pre>'; exit; Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370576 Share on other sites More sharing options...
Agreaves Posted August 19, 2012 Author Share Posted August 19, 2012 ok will do Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370578 Share on other sites More sharing options...
Agreaves Posted August 19, 2012 Author Share Posted August 19, 2012 It printed the information from the Array Array ( [id] => 7261 [Name] => Skull Ashtray [Cat] => Ashtrays [Description] => Skull Ashtray [Price] => 10.00 [Pix] => ../images/ash_trays/7261(150x150).jpg ) Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370580 Share on other sites More sharing options...
jazzman1 Posted August 19, 2012 Share Posted August 19, 2012 Okay, now get rid off exit; from my code, and post the result, again. Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370581 Share on other sites More sharing options...
Agreaves Posted August 19, 2012 Author Share Posted August 19, 2012 It printed the array again plus the pictures. Array ( [id] => 4907 [Name] => Skull Candle Holder [Cat] => Candle Holders [Description] => [Price] => 5.00 [Pix] => ../images/candle_holders/4907.jpg ) Array ( [id] => 5463 [Name] => Skeletal Candle Holder [Cat] => Candle Holders [Description] => [Price] => 5.00 [Pix] => ../images/candle_holders/5463.jpg ) Array ( [id] => 5879 [Name] => Skeletal Candle Holder [Cat] => Candle Holders [Description] => [Price] => 5.00 [Pix] => ../images/candle_holders/5879.jpg ) Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370582 Share on other sites More sharing options...
jazzman1 Posted August 19, 2012 Share Posted August 19, 2012 You don't know how to display this result in html table, is it your problem ? Quote Link to comment https://forums.phpfreaks.com/topic/267244-how-to-display-database-data-to-a-table/#findComment-1370585 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.