|
PHP - Grouping Two Queries / Join Query?
I am using two seperate queries to calculate a streak, but the queries must be grouped together to find the actual streak?
Query 1: SELECT COUNT(matchID) as streak, clan1 FROM webs_cup_matches WHERE ladID='17' AND clan1='2630' AND score1 > score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan1 ORDER BY streak DESC LIMIT 1 Query 2: SELECT COUNT(matchID) as streak, clan2 FROM webs_cup_matches WHERE ladID='17' AND clan2='2630' AND score1 < score2 AND confirmscore='1' AND einspruch='0' GROUP BY clan2 ORDER BY streak DESC LIMIT 1 is it possible someone can join these queries together? Similar TutorialsHi! I'm trying to get working a photo list (library) that I made for my website. I need to query db for somethings and I have to join them all but I'm not very familiarize with this tecquine, neither COUNT/SUM.
First I need to get (all) the images from user_uploads table (imgID, user_id, filename, description, up_time, etc), second get the user info from members table (id, name and usr_img) where id = user_uploads.user_id, third and last make 2 counts to know if the logged in user already liked the img ($_SERVER['user_id']) and get the total likes the current "user_uploads.img_id" have.
I'm already tried many queries, joining, subquery and finally I'm going to try again joining (or inner join). This is what I have:
SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes FROM user_uploads INNER JOIN members AS m ON m.id = uu.user_id -- get img owner info INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ? -- not merge members.id (img owner) with members.id (connected userid) / check if already liked INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID -- get img total likes -- maybe group by, but i've not idea :( ORDER BY up_time DESC -- for the user_uploads.* ???And this is the php code:
if (login_check($mysqli) == true) {
$user_id = $_SESSION['user_id'];
}
else {
$user_id = ip2long(get_ip_address());
}
if ($stmt = $mysqli->prepare(" SELECT user_uploads.* AS uu, members.*, COUNT(img_id, user_id) AS usr_liked, COUNT(img_id) AS total_likes
FROM user_uploads
INNER JOIN members AS m ON m.id = uu.user_id
INNER JOIN img_likes AS il ON il.img_id = uu.imgID AND il.user_id = ?
INNER JOIN img_likes AS ilt ON ilt.img_id = uu.imgID
GROUP BY img_id, user_id
ORDER BY up_time DESC")) {
$stmt->bind_param('i', $user_id);
$stmt->execute(); // get photos
//$Items->store_result();
//$Items->bind_result();
$Items = $stmt->get_result();
foreach ($Items as $ItemInfo) {
$liked = $ItemInfo['usr_liked']; // will this work???
$total_likes = $ItemInfo['total_likes'];
$imgID = $aItemInfo['imgID']; // user_uploads
if ($liked == 0) {
$like = 'Like';
}
else if ($liked == 1) {
$like = 'Unlike';
}
$photo_list .= '
// img
// button
<span class="total_likes" id="">' . $totallikes . '</span>
<a id="' . $total_likes . '" class="likes">' . $like . '</a>
';
}
}
I'm not sure how to do this and I'd appreciate any help.
Thanks in advance!
Hello,
I was wondering if maybe someone could help me on this. I am having trouble with this query. I need to send an email to the users but group all the records related to that user instead of sending one email per record to the user. I hope this makes sense. Here is the code I have so far. All records are being displayed but how can I get all the records for each user (infant_specialist field) send one email and then go to the next user and send the other email and so on.
Thank you for your help
$conn = mysqli_connect('localhost','root','','dbname');
$sql = "SELECT case_no, client_name, infant_specialist.full_name, infant_specialist.is_email FROM ies_clients INNER JOIN infant_specialist ON ies_clients.infant_specialist = infant_specialist.is_id WHERE (date_closed IS NULL OR date_closed >= DATE_FORMAT(NOW() ,'%Y-%m-01')) ORDER BY client_name ASC";
$q = mysqli_query($conn,$sql);
//echo mysqli_num_rows($q) . '<br/>';
while($row = mysqli_fetch_array($q) ){
echo $row['client_name'] . ' --- ' , $row['full_name'] . ' --- ' . $row['is_email'] . '<br/>';
}
Edited by stivenbr, 07 October 2014 - 02:05 PM. Hi, I am currently working on a very detailed personal project during my vacation to keep me busy, but I have a problem, I am trying to create a unique id, without having the chance of a repetition of the id, even if its infinitely small. So I found the flickr ticket id system to be a good choice, since it was sequential and effective. My problem now is, how can I execute 2 queries simultaneously, given the hypothetical change that if I execute them with two separate queries (mysql_query), that there is the chance that another query could be executed in between the break, causing two id's to be the same. Since mysql_query can only execute a single query I am a bit stuck. I thought about possibly using CGI, but then again I have never used it, so I don't know its limitations. Can anyone suggestion anything to me for this problem, as to how I can execute two query strings in the same query. Hi guys,
I’m really hoping someone can help with this query. I'm sure it must use join somehow but i cant work out exactly how to do it.
I have two tables (‘booking_slots’ and ‘booking_reservation’). ‘booking_slots’ has ‘slot_date’ and ‘slot_id’ fields. ‘booking_reservation’ has a number of fields including ‘slot_id’ but not ‘slot_date’.
I want to run a query to delete all records between a certain date range in BOTH tables (say for example 01 Jan 2012 to 01 Jan 2013). To do this I want to find and delete all records using ‘slot_date’ in ‘booking_slots’ and use the corresponding ‘slot_id’ of the deleted records to delete the records with the same ‘slot_id’ in ‘booking_reservation’.
Any help with this would be very greatly appreciated.
Thanks
Hello I have a question. I'm trying to perform multiple queries based off the results from a query. Is this possible ? $result = mysql_query("SELECT id FROM sometable"); for each id returned $result1 = mysql_query("SELECT * FROM someothertable WHERE id=result from $result query"); Any help would be GREAT This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=330871.0 Hello, The query below works well. It pulls information from 3 MySQL tables: login, submission, and comment. It creates a value called totalScore2 based on calculation of values pulled from these three tables. The MySQL tables "comment" and "submission" both have the following fields: Code: [Select] loginid submissionid In the table "submission," each "submissionid" has only one entry/row, and thus only one "loginid" associated with it. In the table "comment," the field "submissionid" could have several entries/rows, and could be associated with multiple "loginid"s. Each time one of the "submissionid"s in "comment" is associated with the same "loginid" that it has in the table "submission," I would like to add this as a factor to the equation below. I would like to multiple instances like this times (-10). How could I do this? Thanks in advance, John $sqlStr2 = "SELECT l.loginid, l.username, l.created, DATEDIFF(NOW(), l.created) + COALESCE(s.total, 0) * 5 + COALESCE(scs.total, 0) * 10 + COALESCE(c.total, 0) AS totalScore2 FROM login l LEFT JOIN ( SELECT loginid, COUNT(1) AS total FROM submission GROUP BY loginid ) s ON l.loginid = s.loginid LEFT JOIN ( SELECT loginid, COUNT(1) AS total FROM comment GROUP BY loginid ) c ON l.loginid = c.loginid LEFT JOIN ( SELECT S2.loginid, COUNT(1) AS total FROM submission S2 INNER JOIN comment C2 ON C2.submissionid = S2.submissionid GROUP BY S2.loginid ) scs ON scs.loginid = l.loginid GROUP BY l.loginid ORDER BY totalScore2 DESC LIMIT 25"; I am having a warning which indicates there is a not valid mysql result, I think the problems lay down at the WHERE clause, but I am not sure. Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /storeprueba/sidebar.php on line 21 Code: [Select] $categoryurl = $_GET['categoryurl']; $sql= mysql_query("SELECT * FROM products, categories WHERE products.category = '$categoryurl' DESC LIMIT 6"); $productCount = mysql_num_rows($sql); // line 21 if ($productCount>0 ) { while($row = mysql_fetch_array($sql)) { $id= $row["id"]; $product_name= $row["product_name"]; $price = $row["price"]; $category = $row["category"]; $subcategory = $row["subcategory"]; $location = $row["location"]; $date_added = strftime("%b %d, %Y", strtotime($row["date_added"])); thanks. Sorry, I originally posted part of this in MYSQL Help, but I was advised to post it here. I'm hoping someone can help me out. I have 2 tables. "categories" and "subcats" (sub categories). I used a JOIN query for both.. Code: [Select] SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name Then I used a while loop to echo all the categories from the "categories" table and their respective sub-categories from the "subcats" table. $sql = "SELECT categories.*, subcats.* FROM categories JOIN subcats on (categories.cat_id = subcats.cat_id) ORDER BY cat_name"; $cats_result = $connection->query($sql) or die(mysqli_error($connection)); while ($row = $cats_result->fetch_assoc()) { $cat_id = $row['cat_id']; $cat_name = $row['cat_name']; $subcat_name = $row['subcat_name']; //should output all categories once and nest their subcategories echo "<strong>".$cat_name."</strong>"; echo "<br />"; echo $subcat_name; echo "<br />"; } No MYSQL errors but unfortunately I get this output.. Technology Computers Technology Gadgets Technology Robots Health Fitness Health Diet What sort of code can use in the loop to echo categories just once instead of multiple times for each topic? Hi, I have a fantasy football website, and on a user account page I want to display fixtures that are coming up that include teams that the current user has chosen. My test_teams table stores all the team names and their teamid. The test_selections table is where each users team selections are stored, it has two columns, userid and teamid. The test_fixtures table has two columns, hometeam and awayteam, these two cloumns hold the teamid of the teams that are playing. The code below correctly displays the fixtures that contain any of the current users team selections. However, it is only displaying the teamid of the teams that are playing as they have not been matched to the test_teams table to get the team name. Does anybody now how I can do this? I believe it can be done using a left join but so far I just keep getting errors when i try to write the code. Any help would be very much appreciated. Code: [Select] <table width="380" border="0"> <?php $query = "SELECT test_fixtures.competition, test_fixtures.date, test_fixtures.hometeam, test_fixtures.awayteam FROM test_fixtures, test_selections WHERE test_selections.userid = '{$_SESSION['userid']}' AND (test_selections.teamid = test_fixtures.hometeam OR test_selections.teamid = test_fixtures.awayteam)"; $result = mysql_query($query) or die(mysql_error()); while($row = mysql_fetch_assoc($result)) { ?> <tr> <td width="85" class="fixtures_date"><?php echo $row['date']; ?></td> <td width="30" class="fixtures_comp"><?php echo $row['competition']; ?></td> <td width="135" class="fixtures_home_teams"><?php echo $row['hometeam']; ?></td> <td width="25" class="fixtures_center">v</td> <td width="135" class="fixtures_away_teams"><?php echo $row['awayteam']; ?></td> </tr> <?php } ?> </table> Hi.. I think I have bad code in three different similar query's in my code. The first query is: $sql="SELECT * FROM invoice as d INNER JOIN members as c ON d.buyer=c.usernum order by " . $orderBy . " " . $order; $result = $con->query($sql); Actually in this one I didn't even know I had a problem other than it was slow working until I put this error trap in:
if (!$check1_res) { The other two pass $id from the previous script they a $sql=mysqli_query($con,"SELECT * FROM invoice_items as d inner JOIN items as c ON d.itemnum=c.itemnum where invnumber = '$id'"); $row = mysqli_fetch_array($sql); and $sql=mysqli_query($con,"SELECT * FROM invoice as d inner JOIN members as c ON d.buyer=c.usernum where invnumber = '$id'"); $row = mysqli_fetch_array($sql); To be honest I didn't know I had a problem with the first two until The third would not return the correct data. I only got partial or none of the invoice data I was expecting. But I realized all three have problems when I used the error trap which come back with this:
Notice: Undefined variable: check1_res in C:\Apache24\htdocs\choo\tc_invoice.php on line 37 The error I get is identical for the first query. At first I thought it was the way that I was passing the variable, but $id is valid when echoed just below the query. So I am hoping that this is going to be something I did wrong with the querys or possibly the fetch statement after the query is wrong??? Really appreciate any help you can give me. If you need to see more of the code on either script please let me know. I was running this query and feel this is slowing down the server: Code: [Select] $agent_query=$this->db->query(" SELECT u.id,u.name,u.team_id,l.agent_id from users AS u,leads AS l WHERE u.id=l.agent_id AND u.team_id IS NOT NULL AND u.is_active='1' ORDER BY u.name ASC "); Is there a better way to write the above code. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=342696.0 It's been a while since I sat down to build some pages and teach myself php. So now that I've started back up, I'm at a loss for what I've done. I deleted a file, and have to rebuild from an old broken version: I have a form that submits a query to the database, but the results pages is giving me this error: Code: [Select] Oops, my query failed. The query is: SELECT COUNT 'descriptors'.* ,'plantae'.* FROM 'descriptors' LEFT JOIN 'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name') WHERE 'leaf_shape' LIKE '%auriculate%' AND 'leaf_venation' LIKE '%%' AND 'leaf_margin' LIKE '%%' The error is: 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 '.* ,'plantae'.* FROM ' at line 2 But I'm not seeing what the syntax error is. Here's the code: Code: [Select] <?php require ('connection.php'); $display = 2; // it's intentionally only 2 for the moment if (isset($_GET['np'])) { $num_pages = $_GET['np']; } else { $data = "SELECT COUNT 'descriptors'.* ,'plantae'.* FROM 'descriptors' LEFT JOIN 'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name') WHERE 'leaf_shape' LIKE '%$s1%' AND 'leaf_venation' LIKE '%$s3%' AND 'leaf_margin' LIKE '%$s4%'"; $result = mysql_query ($data); if (!$result) { die("Oops, my query failed. The query is: <br>$data<br>The error is:<br>".mysql_error()); } $row = mysql_fetch_array($result, MYSQL_NUM); $num_records = $row[0]; if ($num_records > $display) { $num_pages = ceil ($num_records/$display); } else { $num_pages = 1; } } if (isset($_GET['s'])) { $start = $_GET['s']; } else { $start = 0; } if(isset($_POST[submitted])) { // Now collect all info into $item variable $shape = $_POST['s1']; $color = $_POST['s2']; $vein = $_POST['s3']; $margin = $_POST['s4']; // This will take all info from database where row tutorial is $item and collects it into $data variable $data = mysql_query("SELECT 'descriptors'.* ,'plantae'.* FROM 'descriptors' LEFT JOIN 'plantae' ON ('descriptors'.'plant_id' = 'plantae'.'plant_name') WHERE 'leaf_shape` LIKE '%$s1%' AND 'leaf_venation' LIKE '%$s3%' AND 'leaf_margin' LIKE '%$s4%' ORDER BY 'plantae'.'scientific_name` ASC LIMIT $start, $display"); //chs added this in... echo '<table align="center" cellspacing="0" cellpading-"5"> <tr> <td align="left"><b></b></td> <td align="left"><b></b></td> <td align="left"><b>Leaf margin</b></td> <td align="left"><b>Leaf venation</b></td> </tr> '; //end something chs added in // This creates a loop which will repeat itself until there are no more rows to select from the database. We getting the field names and storing them in the $row variable. This makes it easier to echo each field. while($row = mysql_fetch_array($data)){ echo '<tr> <td align="left"> <a href="link.php">View plant</a> </td> <td align="left"> <a href="link.php">unknown link</a> </td> <td align="left">' . $row['scientific_name'] . '</td> <td align="left">' . $row['common_name'] . '</td> <td align="left">' . $row['leaf_shape'] . '</td> </tr>'; } echo '</table>'; // row 95 } if ($num_pages > 1) { echo '<br /><p>'; $current_page = ($start/$display) + 1; // row 100 if ($current_page != 1) { echo '<a href="leafsearch2a.php?s=' . ($start - $display) . '&np=;' . $num_pages . '">Previous</a> '; } for ($i = 1; $i <= $num_pages; $i++) { if($i != $current_page) { echo '<a href="leafsearch2a.php?s=' . (($display * ($i - 1))) . '$np=' . $num_pages . '">' . $i . '</a>'; } else { echo $i . ' '; } } if ($current_page != $num_pages) { echo '<a href="leafsearch2a.php?s=' . ($start + $display) . '$np=' . $num_pages . '"> Next</a>'; } } //added curly ?> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=313679.0 Is there a cleaner way to do this? $query = "SELECT branches.Language FROM eua_users, branches WHERE eua_users.AssignedBranch = branches.country" ; include("dbconnectlocal.php") ; $result = mysql_query($query) ; $row = mysql_fetch_object($result) ; $usrlang = $row->Language ; $query = "SELECT UserName, Email FROM eua_users WHERE UserName = '$user'" ; include("dbconnectlocal.php") ; $result = mysql_query($query) or die(mysql_error()); $row = mysql_fetch_object($result) ; $branchmail = $row->Email ; $query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '0'" ; include("dbconnectlocal.php") ; $result = mysql_query($query) or die(mysql_error()) ; $row = mysql_fetch_object($result) ; $message = $row->$usrlang ; $query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '1'" ; include("dbconnectlocal.php") ; $result = mysql_query($query) or die(mysql_error()) ; $row = mysql_fetch_object($result) ; $url = $row->$usrlang ; $query = "SELECT $usrlang FROM autoreplies WHERE ReplyID = '2'" ; include("dbconnectlocal.php") ; $result = mysql_query($query) or die(mysql_error()) ; $row = mysql_fetch_object($result) ; $subject = $row->$usrlang ; This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308855.0 Hello, I have a family website with an address book. I have it set so that you can select people based on who's family they are part of. So everyone in the family is part of my grandmother so when you select her group then everyone should display. Then I have it so that when you select my aunt or uncle then it will display only their families and so forth. so everyone except my grandmother has more than one family_id. I have a master_id specific to each master_name, then each master_id can have up to 3 family_id's. my issue is when I select all (my grandmother's group) i need it to group by my aunt's and uncle's families. and right now it is just in order of how I entered it into the database. My query selects all family_id's that = my $_POST[fam_id] from a dropdown list. So when I select my grandmother's group it will only return family_id "1". but i need to group by family_id 2-15, whichever is assigned to my aunt or uncle under my grandmother. since the query does not ask for it obviously i cannot sort by it. If I ask for everyone, grouped by family_id then I cannot select only by aunt or uncle as well. i'm at a total loss. please help. Here is my basic query right now: $sel_id= "SELECT master_id FROM master_family where family_id = '$_POST[fam_id]' ORDER BY master_id"; Please know this is my first website so I am very new to this and hope my question is not too lame. Hello all, Need a little help with grouping and summing using an array. I can't do this directly from the database because the vendor package we are using (Tivoli Data Warehouse) stores date in a proprietary format as a CHAR in the database and in GMT so I have to get the data first and then manipulate the date to get it human readable and in the right EST date. I have my query (and my date conversion) from the database returning the data in a format that looks like this: The reason each date has the service repeated is because it was selected from the database by hour (needed to convert Tivoli's weird timestamp and use of GMT) Service TX_Count Date ------------------------------------------------------------------------------------------- Service1 23451 2010-01-01 Service1 93874 2010-01-01 Service1 82363 2010-01-01 Service1 56245 2010-01-02 Service1 73453 2010-01-02 Service1 18965 2010-01-02 I have successfully gotten the data in to an array and I can group by date and then sum the tx_count, or I can group the services and sum the tx_count. What I really need to be able to do is to get the transaction counts by service by day so that I would get something like this as a result Service1 2010-01-01 199688 Service1 2010-01-02 148663 So I need to group by service and then date and then sum it all out of the array. Any advice and direction is appreciated |
|||||||