|
PHP - Mysql Query Comparing Columns
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. Similar TutorialsI 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 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'"); The code below is a function that checks to see if an email address exists in a database, if so it alerts the user. The db has one table and one field. It works fine when there is ONE record! However, if there are > 1 it doesn't work. How can I step through each record and compare it to what the user entered? Of course, $_POST is the user's value and the db record is the $myAddy value. <?php function emailLookup() { include ('file:///Library/WebServer/Documents/re_connect_scripts/emailLookup.php'); while ($row = mysqli_fetch_array($result)) { extract ($row); $myAddy = $addy; } if ($_POST["add_email"] == $myAddy) { global $lookupError; $lookupError = 'This email address is already on the list.'; global $counter; $counter++; } else { return; } } ?> Well I'm hoping someone will have some fun with this. My database has a list of times for a given date, and a schedule for each time. I'm trying to change the input backgrounds for rows between 2 different times. Like this: 8:00 enter some data 8:10 enter some data 8:20 enter some data etc. What I want is to change the background color of any input field between say 9:00 and 10:00, but leave everything else white. I can change the color, just can't figure out how to compare the times. Here's part of the code: Code: [Select] while($row = mysql_fetch_array( $result )) { $opentimeID = date("g:i",strtotime(1530)); //not sure if these statements are correct. $closetimeID = date("g:i",strtotime(1730)); if (strtotime($row['Time']) //...not sure how to finish this statement to compare the time in the database row to $opentime and $closetime. { echo "<tr><td>"; echo date("g:i ", strtotime($row[Time])); echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player1]' name='player1[$i]' style ='background-color: #FFFF66;'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player2]' name='player2[$i]' style ='background-color: #FFFF66;'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player3]' name='player3[$i]' style ='background-color: #FFFF66;'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player4]' name='player4[$i]' style ='background-color: #FFFF66;'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player5]' name='player5[$i]' style ='background-color: #FFFF66;'>"; } else { echo "<tr>"; echo "<td>"; echo date("g:i ", strtotime($row[Time])); echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player1]' name='player1[$i]'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player2]' name='player2[$i]'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player3]' name='player3[$i]'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player4]' name='player4[$i]'>"; echo "</td><td>"; echo "<input type='text' size='20' value= '$row[Player5]' name='player5[$i]'>"; } Thanks for any advice! Hey guys, im having a slight issue with comparing mysql time stamp. I'm using the timestamp in mysql to show how many people are online with in 5 minutes. $sql = mysql_query("SELECT name,id FROM users WHERE DATE_SUB(NOW(), INTERVAL 5 MINUTE) <= last_active ORDER BY id ASC"); But I want to grab last_active from the data base and compare it like below. I dont know if strtotime just doesnt compare with mysql time stamp or what? if($last_active > strtotime('-5 minutes')) { echo "<td>Online</td>"; }else{ echo "<td>Offline</td>"; } Hi there, This is what I need to do, blow that is the code I have knocked up to do it but it doesn't seem to work. The main problem is the query that matches the atc callsigns to the pilots destinations.. can't fathom it myself. 1. Pull all the records from my database. 2. Assign their unique user id's, callsigns and destinations. 3. Execute a query in which you: SELECT * FROM CLIENTS WHERE (The 'clienttype' = 'ATC') AND match the callsign of the ATC users to the 'planned_destairport' of the 'clienttype' = 'PILOT'. For example if three people are going to Heathrow, then it would return 3 results. 4. Then do a mysql_num_rows of the result. 5. Lastly, execute a query that would update the 'atcarrivals' collumn in the database with the count number using the unique 'cid' as the identifier. All in a loop of course. // Selects all of the ATC Records. $atc_arrivals_selectquery = mysql_query ("SELECT * FROM CLIENTS"); // Starts the loop for fetching the records of the previous query. while($atc_arrivals_row = mysql_fetch_array($atc_arrivals_selectquery)){ // Sets the users cid, planned_destairport and callsign. $atc_cid = $atc_arrivals_row['cid']; $atc_destinations = $atc_arrivals_row['planned_destaiport']; $atc_callysign = $atc_arrivals_row['callsign']; // Searches the database and selects all records where the callsign is LIKE the destination airport. $atc_arrivals_check = mysql_query("SELECT * FROM CLIENTS WHERE clienttype = 'ATC' AND callsign LIKE '%$atc_destinations'"); // Counts previous query results. $atc_arrivals_count = mysql_num_rows($atc_arrivals_check); // Updates the atcarrivals column with the count.. $atc_arrivals_updatequery = mysql_query("UPDATE CLIENTS SET atcarrivals = '$atc_arrivals_count' WHERE cid = '$atc_cid' AND clienttype = 'ATC'"); // End loop. } Concerned columns are these: "cid" - Unique client id. "atcarrivals" - Number of arrivals from count. (This is the problem.) "planned_destairport" - Destination airport of pilot. Usually four letters long. IE. EGLL = Heathrow. Thanks. Here is my code: // Start MySQL Query for Records $query = "SELECT codes_update_no_join_1b" . "SET orig_code_1 = new_code_1, orig_code_2 = new_code_2" . "WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)"; $results = mysql_query($query) or die(mysql_error()); // End MySQL Query for Records This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this??? Code: [Select] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1 If you also have any feedback on my code, please do tell me. I wish to improve my coding base. Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference. Code: Code: [Select] mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); Full code: Code: [Select] <?php include_once("includes/config.php"); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <title><? $title; ?></title> <meta http-equiv="Content-Language" content="English" /> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <link rel="stylesheet" type="text/css" href="style.css" media="screen" /> </head> <body> <div id="wrap"> <div id="header"> <h1><? $title; ?></h1> <h2><? $description; ?></h2> </div> <? include_once("includes/navigation.php"); ?> <div id="content"> <div id="right"> <h2>Create</h2> <div id="artlicles"> <?php if(!$_SESSION['user']) { $username = mysql_real_escape_string($_POST['username']); $password = mysql_real_escape_string($_POST['password']); $name = mysql_real_escape_string($_POST['name']); $server_type = mysql_real_escape_string($_POST['type']); $description = mysql_real_escape_string($_POST['description']); if(!$username || !$password || !$server_type || !$description || !$name) { echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>"; echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'> <option value='Any'>Any</option> <option value='PvP'>PvP</option> <option value='Creative'>Creative</option> <option value='Survival'>Survival</option> <option value='Roleplay'>RolePlay</option> </select></td></tr> <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>"; echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>"; } elseif(strlen($password) < 8) { echo "Password needs to be higher than 8 characters!"; } elseif(strlen($username) > 13) { echo "Username can't be greater than 13 characters!"; } else { $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1"); if(mysql_num_rows($check1) < 0) { echo "Sorry, there is already an account with this username and/or server name!"; } else { $ip = $_SERVER['REMOTE_ADDR']; mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)"); echo "Server has been succesfully created!"; } } } else { echo "You are currently logged in!"; } ?> </div> </div> <div style="clear: both;"> </div> </div> <div id="footer"> <a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop </div> </div> </body> </html> 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! 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!
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! 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, 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". 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. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313325.0 I created a simple search box which will query my table and match the input value to one of my columns. two of these columns store comma separated values. if i query a column other than a column which stores my csv i can see my search results. if i query a column which stores my csv i will not see results unless the search value matches the first value within the column. how would i be able to get say the second or third or forth value. here is the code i am using to query the table any help would be appreciated thanks. Code: [Select] $q = $this->db->query("SELECT * FROM table WHERE col1 LIKE 'searchvalue'". " OR col2 LIKE 'searchvalue'". " OR col3 LIKE 'searchvalue'". " OR col4 LIKE 'searchvalue'". " OR FIND_IN_SET('searchvalue', col5) > 0 ". " OR FIND_IN_SET('searchvalue', col6) > 0"); I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate. What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop. It doesn't matter if I get to the next page using a header redirect or a form submit. I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. Any ideas? This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=351561.0 |
|||||||