|
PHP - Mysql & Php: Associate Columns Into Another Table
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!
Similar Tutorials
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! 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. 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! I have created a basic website which allows users to login and then need to record their location. I have it so they enter their location into a form, but I need to associate the table for users with locations. I used the code: $user = R::findOne('person', 'username=?', array($_SESSION['name'])); $plc = R::dispense('location'); $plc->location = $_REQUEST['location']; $plc->activity = $_REQUEST['activity']; $id = R::store($plc); R::associate($user, $plc ); It returns the error: Catchable fatal error: Argument 1 passed to R::associate() must be an instance of RedBean_OODBBean, boolean given HELP???!!!! 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. 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.. 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?
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 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 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 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 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'"); so I have this code: Code: [Select] <input type='checkbox' name='title8' value='$title8' /></td><td><strong>$title8</strong> <br /> $date8 <br />$ $price8<input type='hidden' name='price8' value='$price8'> how do I make is so that the hidden field is only going to submit if the checkbox is checked........ 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) 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!
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 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? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313325.0 I am just trying to use Associate array in another php file through session.
file1.php <?php $age = array(); $_SESSION["age"] = array("Peter"=>"35", "Ben"=>"37", "Joe"=>"43"); foreach($_SESSION["age"] as $x => $x_value) { echo "Key=" . $x . ", Value=" . $x_value; echo "<br>\n"; } ?> OUTPUT: Key=Peter, Value=35 Key=Ben, Value=37 Key=Joe, Value=43 file2.php <?php session_start(); foreach( $_SESSION["age"] as $x => $x_value) { echo "Key=" . $x . ", Value=" . $x_value; echo "<br>\n"; } ?> OUTPUT: PHP Notice: Undefined index: age in session.php on line 3 PHP Warning: Invalid argument supplied for foreach() in session.php on line 3 Why am I getting this error? Above is just example I tried to demonstrate for asking question for other issue, unfortunately I got new error in this example. But actual issue was, I was trying to plot graph using library which requires only Associate array, where the data is formed in file1.php. But I get only last index value displayed in file2.php even in graph or in echo. I don't understand why other values are not displayed in file2.php Please point me what is going wrong, Thanks. Code: [Select] Array ( [Comment] => Array ( [post_id] => 1 [name] => cc [email] => [body] => spam ) ) How can i check if email is empty or not? Is there any php built in function? Should i use foreach? |
|||||||