|
PHP - Looping Through Mysql Table Columns
So I have a database that is structured like this: https://imgur.com/a/DdyTqiE
I would like to loop through this table, and get a count of how many were 'is_no_show' and 'is_cancelled' per (unique) category with respect to 'scheduled_student_services.' How would I approach this? I know I probably need to do two loops but I'm not sure the PHP syntax for this. Any suggestions or tips would be helpful. Thank you for your time! Similar TutorialsHi all I have 2 tables: 1. is a tables full of images that have an id, a src and a title (images_table). 2. table is a list of records with an id, a description and an images_id (records_table) What I'm looking to do is: Loop through the records in the records_table and find the description, the description is something like this 'Welcome to Kansas'. Then, I want to loop through the images_table and find the associated image of Kansas, based on a MySQL LIKE statement. This is because the images_table has a title of 'Kansas', for example. Once this has been done, I then need to insert the id of Kansas into the images_id in the records_table a possible MySQL UPDATE. Has anyone got an idea how I could do this? Thanks Hey guys seem to have tricky question here, I am just wondering how I could do the following:
I have an SQL table that contains these columns:
As you can see there is a 'Make' column and a 'Model' column and each row displays what make it is.
I now want to associate the 'Makes' with the 'Models' in another table just like this example:
BMW AUDI VOLVO FORD TOYOTA
X5 All Road XC90 Focus Supra
3 Series A1 C30 Mustang Yaris
5 Series Galaxy
So for example if the first table has a row that contains the make 'AUDI' within the 'Make' column I then want it to take the value of the 'Model' and list it into my other table under the 'AUDI' column, any ideas how I can do this with PHP?
Thanks!
Hello, I have a script that processes data from a form, and then outputs data from a MySQL table based on which selections are made on the form. What I'm trying to do is create columns showing data for each month depending on which months the user selects, and then a "Total" column at the end of that. The output first determines the "family" the results are in, then the ID the product is for the parent family, and then it outputs the data based on what ID is listed. Unfortunately, I have no idea how to do this without nesting while loops inside other while loops which I know is bad for server performance - and yes this does take a long time to complete. Here is some of my code for this to show what I'm doing wrong: Code: [Select] $searchdatabase5 = "SELECT field3 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 != 0 AND field4 != '995' GROUP BY field3"; $run5 = mysql_query($searchdatabase5) or die(mysql_error()); while($row5 = mysql_fetch_assoc($run5)) { $field5_3 = $row5['field3']; echo '<tr><td colspan="0"><hr style="width: 100%; height: 1px; color: #a0a0a0;"></td></tr><tr><td colspan="0" bgcolor="#ccffcc"><strong>FAMILY '. $field5_3 .'</strong><br /></td></tr>'; $searchdatabase12 = "SELECT field4 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field3 = '$field5_3' AND field4 != 0 GROUP BY field4"; $run12 = mysql_query($searchdatabase12) or die(mysql_error()); while($row12 = mysql_fetch_assoc($run12)) { $field12_4 = $row12['field4']; $searchdatabase6 = "SELECT field3,field4,field5,field6 FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field4 = '$field12_4' GROUP BY field3,field4,field5"; $run6 = mysql_query($searchdatabase6) or die(mysql_error()); while($row6 = mysql_fetch_assoc($run6)) { $field6_3 = $row6['field3']; $field6_4 = $row6['field4']; $field6_5 = $row6['field5']; $field6_6 = $row6['field6']; echo '<tr><td colspan="0">'. $field6_5 .' - '. $field6_6 .'</td></tr><tr><td valign="middle" align="left" bgcolor="#ccc" width="183px">Units</td>'; $searchdatabase7 = "SELECT * FROM slssum2 WHERE company = '$companyname' AND CONCAT(year,month) BETWEEN '$startyear$startmonth' AND '$endyear$endmonth' AND field5 = '$field6_5' ORDER BY year,month ASC"; $run7 = mysql_query($searchdatabase7) or die(mysql_error()); while($row7 = mysql_fetch_assoc($run7)) { $field7_3 = $row7['field3']; $field7_4 = $row7['field4']; $field7_5 = $row7['field5']; $field7_9 = $row7['field9']; $field7_10 = $row7['field10']; $field7_month = $row7['month']; $field7_year = $row7['year']; $startmonth2 = ltrim($startmonth, '0'); echo '<td bgcolor="#e6e6e6" width="95px" nowrap="nowrap" align="right">'; echo $field7_10; echo '</td>'; } Here is the output: I know the code is very bad, but what would be the best way to recreate what I have in the image? Thank you. Apologies if this should be in the mysql forum but hopefully this is the correct one. I am trying to extract the sum of a column from my database for each financial year, at the moment I am doing each year as a separate recordset but I am certain there must be a more automated way! The financial year is 1st April to 31st March each year and I need to create a variable which is a sum of each year, you'll see in my code below what I mean, any help gratefully appreciated as I am going to end up with decades of info in the db and am keen to get the code right now! The relevant recordsets are 2,5,7. db is readingID, date, reading The code and page does work fine, it's just long winded! Code: [Select] <?php mysql_select_db($database_wind, $wind); $query_Recordset1 = "SELECT readingID, date_format(date,'%d/%m/%Y') as date, reading FROM solar ORDER BY readingID DESC LIMIT 5"; $Recordset1 = mysql_query($query_Recordset1, $wind) or die(mysql_error()); $row_Recordset1 = mysql_fetch_assoc($Recordset1); $totalRows_Recordset1 = mysql_num_rows($Recordset1); mysql_select_db($database_wind, $wind); $query_Recordset2 = "SELECT SUM(reading) as year1total FROM `solar` WHERE date between '2010-04-01' and '2011-03-31'"; $Recordset2 = mysql_query($query_Recordset2, $wind) or die(mysql_error()); $row_Recordset2 = mysql_fetch_assoc($Recordset2); $totalRows_Recordset2 = mysql_num_rows($Recordset2); mysql_select_db($database_wind, $wind); $query_Recordset3 = "SELECT * FROM solar ORDER BY readingID DESC Limit 29"; $Recordset3 = mysql_query($query_Recordset3) or die(mysql_error()); mysql_select_db($database_wind, $wind); $query_Recordset4 = "SELECT readingID, YEAR(date) as yeardate, MONTHNAME(date) as monthdate, SUM(reading) as sumreading FROM `solar` Group by yeardate, monthdate Order by readingID ASC"; $Recordset4 = mysql_query($query_Recordset4) or die(mysql_error()); mysql_select_db($database_wind, $wind); $query_Recordset5 = "SELECT SUM(reading) as year2total FROM `solar` WHERE date between '2011-04-01' and '2012-03-31'"; $Recordset5 = mysql_query($query_Recordset5, $wind) or die(mysql_error()); $row_Recordset5 = mysql_fetch_assoc($Recordset5); $totalRows_Recordset5 = mysql_num_rows($Recordset5); mysql_select_db($database_wind, $wind); $query_Recordset6 = "SELECT datediff( Max(date), Min(date)) as DateDiff, Sum(reading) as LatestReading, date_format(MAX(date),'%d/%m/%Y') as LatestDate FROM solar"; $Recordset6 = mysql_query($query_Recordset6, $wind) or die(mysql_error()); $row_Recordset6 = mysql_fetch_assoc($Recordset6); $totalRows_Recordset6 = mysql_num_rows($Recordset6); mysql_select_db($database_wind, $wind); $query_Recordset7 = "SELECT SUM(reading) as year3total FROM `solar` WHERE date between '2012-04-01' and '2013-03-31'"; $Recordset7 = mysql_query($query_Recordset7, $wind) or die(mysql_error()); $row_Recordset7 = mysql_fetch_assoc($Recordset7); $totalRows_Recordset7 = mysql_num_rows($Recordset7); $average = (int)(($row_Recordset6['LatestReading'])/($row_Recordset6['DateDiff'])); $income1 = $row_Recordset2['year1total']*0.428; $income2 = $income1 + $row_Recordset5['year2total']*0.464; $income = $income2 + $row_Recordset7['year3total']*0.464; $saving = $row_Recordset2['year1total']*0.0675; $saving = $saving + $row_Recordset5['year2total']*0.0675; $saving = $saving + $row_Recordset7['year3total']*0.0675; ?> Here is my java script
$.each(data, function() {
var myTable1 =
'<tr bgcolor=#ffffff>' +
'<td align=center>'+this.vehiclelog_plate+'</td>' +
'<td align=center>'+this.vehiclelog_name+'</td>' +
'<td align=center>'+this.vehiclelog_date+'</td>' +
'<td align=center>'+this.vehiclelog_reftype+'</td>' +
'<td align=center>'+this.vehiclelog_refid+'</td>' +
'<td align=center>'+this.vehiclelog_description+'</td>' +
'</tr>';
$('#tableField').append(myTable1);
});
And here is my HTML
<div id="tabs-6">
<div id="rform">
<form action = "home.php" method="post">
<fieldset>
<legend>View Vehicle Service</legend><br><br>
<div>
<label class="label-left">Vehicle Name:</label>
<span role="status" aria-live="polite" class="ui-helper-hidden-accessible"></span>
<input id="autocomplete" title="type "a"" class="ui-autocomplete-input" name="vehicle_service_search" autocomplete="off">
</div>
<div>
<button id="searchVehicleDesc" class="ui-button ui-widget ui-state-default ui-corner-all ui-button-text-only" role="button" aria-disabled="false">
<span class="ui-button-text">View</span>
</button>
</div>
<div>
<table border="1" width="650" id = "tableField">
<tr bgcolor=#c0c0c0>
<td width=100 align="center"><font face="helvetica"><b>Vehicle Name</b></font></td>
<td width=80 align="center"><font face="helvetica"><b>Service Type</b></font></td>
<td width=80 align="center"><font face="helvetica"><b>Others</b></font></td>
<td width=100 align="center"><font face="helvetica"><b>Reference No.</b></font></td>
<td width=80 align="center"><font face="helvetica"><b>Reference Date</b></font></td>
<td width=80 align="center"><font face="helvetica"><b>Reference Type</b></font></td>
</tr>
</table>
</div>
</fieldset>
</form>
</div>
</div>
And here is my response
[{"success":1,"plate":"SIX666","name":"YAMAHA ISUZU","date":"0000-00-00","type":"Add Vehicle","id":"0","desc":""},{"success":1,"plate":"VIE597","name":"Resource id #6","date":"2014-05-09","type":"OIL","id":"332142","desc":"castroil"}]
Im getting the right response from ajax but I can't put my value to a table I get an alert that says undefined.
No idea what I am doing here. I have a joined table which is:
$sql = "SELECT itemnum, image1, title, close, quantity, bidquantity, ". I need to loop through the table on buser and insert select data into two other tables. One of the tables is the control table which will only have one row per buser. The other table I guess you would call the data table of the two and may have more than one row depending. The catch is these will be linked together by an invoice number. Table A, the control table has a field for invoice number, table B does not. Of course once the data is inserted into the new tables they will be joined and from there on it should be easy. I just don't know now how to get there. I've thought about using an array to loop through, but I don't know enough about them to make any sense of this. Not even sure the group by may help me in doing this. Anyone have any ideas on this? I sure do need it and thanks in advance! Hello, I'm new! I am trying to populate a list of locations based on ratings. I can populate the list just fine but it's displaying every record in the table. Is there a way of only looping 4 times so as to just display the top 4 records? Here's the code: Code: [Select] $result = mysql_query("SELECT * FROM locations ORDER BY rating DESC"); while($row = mysql_fetch_array($result)) { echo $row['name']; echo "<br>; } Thankyou in advance for any help I have looked all over the net on how to fix this... I am creating a baseball statistical website and I have 2 tables with identical columns (2012hitting and 2013hitting). I am trying to create a page where I can 1) group each year's statistics (which I have been able to do) and 2) have a row named "Career Totals". Each table has about 15 columns, various stats. How can I add the data from my 2012hitting table to my 2013hitting table into a Career Totals row for each player? This is what I'm trying now: Code: [Select] /* CONNECTION VARIABLES */ $id = $_GET['id']; // get var from URL /* Get data. */ $sql = "SELECT * (sum(2012hitting.hr) +sum(2013hitting.hr)) as totalhr FROM 2012hitting, 2013hitting WHERE id='$id'"; $result = mysql_query($sql); ?> <?php $alternate = "2"; while ($row = mysql_fetch_array($result)) { $field1 = $row["season"]; $field2 = $row["team"]; $field3 = $row["games"]; $field4 = $row["ave"]; $field5 = $row["slg"]; $field6 = $row["r"]; $field7 = $row["h"]; $field8 = $row["rbi"]; $field9 = $row["bb"]; $field10 = $row["k"]; $field11 = $row["hr"]; $field12 = $row["dbl"]; $field13 = $row["tpl"]; $field14 = $row["sb"]; $field15 = $row["obp"]; $field16 = $row["ops"]; if ($alternate == "1") { $color = "#ffffff"; $alternate = "2"; } else { $color = "#E4E4E4"; $alternate = "1"; } echo "<tr bgcolor=$color><td align='center'>$field1</td><td align='center'>$field2</td><td align='center'>$field3</td><td align='center'>$field4</td><td align='center'>$field5</td><td align='center'>$field6</td><td align='center'>$field7</td><td align='center'>$field8</td><td align='center'>$field9</td><td align='center'>$field10</td><td align='center'>$field11</td><td align='center'>$field12</td><td align='center'>$field13</td><td align='center'>$field14</td><td align='center'>$field15</td><td align='center'>$field16</td></tr>"; } echo "</table>"; ?> Thank you in advance! This: Code: [Select] echo "<pre>"; print_r($torrent->content()); echo "</pre>"; Returns: Code: [Select] Array ( [Eureka.S05E01.HDTV.XviD-ASAP\Eureka.S05E01.HDTV.XviD-ASAP.avi] => 367011826 [Eureka.S05E01.HDTV.XviD-ASAP\eureka.s05e01.hdtv.xvid-asap.nfo] => 4798 [Eureka.S05E01.HDTV.XviD-ASAP\Sample\eureka.s05e01.hdtv.xvid-asap.sample.avi] => 7883092 ) Needs to be inserted to MySQL like: In other words, the array $torrent->content() needs to be inserted to MySQL as new lines at columns "filename" and "size". Please help me.. I have a table called "friends" and 4 columns called: id......friend_1......friend_2.......date I want to retrieve all of the friends of the user that is logged in, so if the table looks like: friend_1.........friend_2 3.........................6 6.........................8 12.......................3 the result if the users id is 3 would be: 6 12 and if the users id was 6 it would be: 3 8 the query I have right now partially works, just note sure how to modify it to get the full result I want. Code: [Select] $get_friends = mysql_query("SELECT * FROM friends WHERE (friend_1='".$session."' OR friend_2='".$session."')"); while($friends = mysql_fetch_array($get_friends)) { print $friends['friend_1']; }with the code above if you use the table I showed above and assumed that $session = 3, the result is: 3 12 but should be: 6 12 hopefully that's pretty straight forward, thanks in advanced. Hi,
I am trying to make a list of all countries listed in a database and the number of times they appear. But I am clearly doing something wrong as I am not able to list out the number of times that they appear.
This is what I have got so far:
<h4>Countries</h4>
<table><col width='150px'><col width='150px'>
<tr>
<th>
Country
</th>
<th>
Number of Occurences
</th>
</tr>
<?php
$location = DB::getInstance()->query("SELECT country, COUNT(country) as countCountry FROM users GROUP BY country ORDER BY count DESC");
foreach ($location->results() as $locations)
{
echo "<tr><td>";
echo $locations->country;
echo "</td><td>";
echo $locations->countCountry;
echo "</td></tr>";
}
?>
</table>
Can anyone point me in the right direction?
Hi, This has been baffling me for a couple hours now and i cant seem to figure it out. I have some code which creates an array and gets info from a mysql database and then displays in a list. This works great but after adding more and more rows to my database the list is now becoming quite large and doesnt look great on my site. Is it possible to split the list into multiple columns of about 25 and if possible once 3 or 4 columns have been created start another column underneath. To help explain i would be looking at a layout as follows: Code: [Select] line 1 line 1 line 1 line 2 line 2 line 2 ... ... ... line 25 line 25 line 25 line 1 line 1 line 1 line 2 line 2 line 2 ... ... ... line 25 line 25 line 25Im guessing there should be some sort of if statement to check how many items are being displayed and to create a new column if necessary. Is this correct? Thanks, Alex I have a search set up to search a table for the text entered in a textbox, I have two columns in the table, one with the first name of people, and the second with their last names, I am wondering how I can search both, so for instance: I type in the search field: Roger Smith in the database it would look like: First_name-----|-----Last_name -------------------|------------------- Roger------------|-------Smith my current query is: Code: [Select] $query = mysql_query("SELECT * FROM users WHERE fname LIKE '%$find%' OR lname LIKE '%$find%'"); But if I type both parts of the name it doesn't return anything. works fine if I just search for "Roger" OR "Smith". I am loading notifications from a database table called "notifications" and I am having a little trouble getting them to order in the correct way. my query I'm using right now: Code: [Select] $query = mysql_query("SELECT B.* FROM (SELECT A.* FROM notifications A WHERE A.user_id='$session' AND A.from_id!='$session' ORDER BY A.id ASC ) AS B ORDER BY B.state ASC LIMIT 7"); this works well as far as showing the unread notifications on top, then the read notifications below, however it's not ordering the two sets by ID (which the id auto increments so the higher id number is the newest) from newest on top to the oldest on bottom, still keeping them separated by the unread on top, read on bottom (column name is state for showing whether they're read or not). The order the notifications are displaying by their ID is: 3 5 2 4 1 when it should be: 5 3 2 4 1 I create a post just now but cannot seem to find it to add to it. Anyhow, I have 2 tables, I need to multiply qty from the one table to the mass from the other to get a total, which I have achieved with this code:
$sql_total_mass = "
SELECT jobs_assembly.assemble_qty, jobs.mass,
(jobs_assembly.assemble_qty * jobs.mass) AS 'sum'
FROM jobs_assembly
LEFT JOIN jobs on jobs_assembly.jobs_id = jobs.id
LEFT JOIN job_names ON jobs.job_names_id = job_names.job_id
WHERE jobs_assembly.assemble_date = '$link_date'
ORDER BY job_names.job_name, jobs.assembly
";
$result_total_mass = mysqli_query($conn, $sql_total_mass);
if (mysqli_num_rows($result_total_mass) > 0) {
while($row_total_mass = mysqli_fetch_assoc($result_total_mass)) {
echo $row_total_mass['sum'].'<br />';
} //while
} //if
Now I need to take all these totals and make a grand total. So basically add all the results from $row_total_mass[‘sum’] together and show it.
I made a small editing system for my news page, and I need to update three columns within my table "announcements" in the database. I tried a method of updating all of them with one MySQL query instead of using three as it just isn't neat. I've searched several methods via google and I've tried all of them, but just can't seem to get it to work. Is this MySQL query correct? mysql_query("UPDATE announcements SET title = {$title} WHERE id = '$id', content = {$content} WHERE id = '$id', lastmodified = ". date('M-d-Y') ." WHERE id = '$id'"); Hi, Mary Christmas My result of mysql database is LIMIT 8; now i have to retrieve and show this to two Horizontal columns As each columns 4 result. how to generate this Without tables!! only with DIV or UL/LI list . Thanks Hello everybody,
I have been racking my brain trying to decipher something that should be really simple for a seasoned programmer. Newbieprogrammer
<html>
function display($randomNr) When displaying data from a mysql table, what php code can I use to display the number of columns in said table? Like, say I'm displaying comments by users. I want to be able to put "displaying 'thismany' comments". Any suggestions? Hello,
I have this very frustrating problem I'm trying to make a dynamic table with only 5 columns per row. So every 5 items, I need a new row. I have tried many different examples with no success. What is the best way to approach this? Here is what I am working with, this doesn't show what I have tried, just what I am working with at the moment which of course, just outputs it one column per row. http://www.mesquitew.../inc-legend.php
// Lets parse the data
$entries = simplexml_load_file($data);
if(count($entries)):
//Registering NameSpace
$entries->registerXPathNamespace('prefix', 'http://www.w3.org/2005/Atom');
$result = $entries->xpath("//prefix:entry");
foreach ($result as $entry):
$event = $entry->children("cap", true)->event;
// Set the alert colors for the legend
include ('../inc-NWR-alert-colors.php');
// Lets creat some styles for the list
$spanStyle = "background-color:{$alertColor};border:solid 1px #333;width:15px;height:10px;display:inline-block;'> </span><span style='font-size:12px;color:#555;";
$legend .= "<table>";
$legend .= "<tr>";
$legend .= "<td> <span style='$spanStyle'> $event</span></td>";
$legend .= "</tr>";
$legend .= "</table>";
endforeach;
endif;
echo $legend;
-Thanks!
|
|||||||