PHP - Do I Need Multiple Queries To Do What I'm Doing?
The whole code is below, followed by the part that isn't producing. It all works extremely well until the last part, and I have two instances of setting a variable to mysql_fetch_assoc that work. Not sure why the third attempt doesn't. I'm just trying to have a separate area that outputs 'msg'.
Code: [Select] $sql = "SELECT * FROM players as p INNER JOIN schools as s WHERE p.tid = s.id ORDER BY status, playerLast"; $results = mysql_query($sql); echo '<div class="roster">'; $team = mysql_fetch_assoc($results); echo '<div class="team_info"> <div class="school">' . $team['school'] . '</div> <div class="coach">Coach ' .$team['coachFirst'] . ' ' . $team['coachLast'] .'</div> <div>Sectional: ' . $team['sectional'] . '</div> <div>Class: ' . $team['class'] . 'A</div> '; echo '</div>'; $currentStatus = false; //Flag to detect change in status while($player = mysql_fetch_assoc($results)) { if($currentStatus != $player['status']) { //Status has changed, display status header $currentStatus = $player['status']; echo '<br><b>'; if ($currentStatus == '1') {echo 'Returning Starters';} elseif ($currentStatus == '2') {echo 'Key Returners';} elseif ($currentStatus == '3') {echo 'Varsity Newcomers';} elseif ($currentStatus == '4') {echo 'Key Freshmen';} echo '</b><br>'; // echo "<div><b>{$currentStatus}</b></div>\n"; } //Display player info echo $player['playerFirst'] . ' ' . $player['playerLast'] . ', ' . $player['feet'] . '\'' . $player['inches'] . '",' . $player['position'] . ', ' . $player['year'] . ';<br>'; } //Player comments echo '<hr>'; echo '<div class="coach_comments"><span class="player_name">hello'; while($comments = mysql_fetch_assoc($results)) { echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ': </span>' . $comments['msg']; } echo '</div>'; echo '</div>'; It's all working until what's below. All I get out of it is the "hello", which is just a test to make sure it wasn't a CSS issue. Quote //Player comments echo '<hr>'; echo '<div class="coach_comments"><span class="player_name">hello'; while($comments = mysql_fetch_assoc($results)) { echo $comments['playerFisrt'] . ' ' . $comments['playerLast'] . ': </span>' . $comments['msg']; } echo '</div>'; echo '</div>'; Similar TutorialsHi everyone I have the following PHP code $sql.= "INSERT INTO `data` (`info`, `write`, `date`) VALUES"; $sql.= "("; for ($c=0; $c < $num; $c++) { $sql.= '"'.str_replace('"', "", $data[$c]).'",'; } $sql.= "'".date('Y-m-d')."');"; If I print $sql; I get Code: [Select] INSERT INTO `data` (`info`, `write`, `date`) VALUES("data99","n",'2010-10-05'); INSERT INTO `data` (`info`, `write`, `date`) VALUES("data101","y",'2010-10-05'); INSERT INTO `data` (`info`, `write`, `date`) VALUES("data876","n",'2010-10-05'); what would I need to do to my PHP code in order for it to process each QUERY either all at once or one at a time Whatever I try, it either inserts nothing, or just does the first INSERT Any ideas? Thanks Hi all, I would like to perform two queries in a mysqli statement before closing the connection. I'm using prepared statements and I've read on the php.net you can perform a multi-query using mysqli->multi_query. My problem is that I'm only finding useful examples on how to select and fetch results not using prepared statements. I'm a bit lost with how to do this to update two tables. Currently i'm using two seperate connections to do the queries but want to combine these together: Query one (updates their password and resets number of logins to force password change on next login): Code: [Select] //reset logins to force the user to change password upon next login $loggedin = '0'; // connect to db for mysqli require('../db/db.php'); // updates password and number of logins $insert_stmt = $mysqli->stmt_init(); if ($insert_stmt->prepare("UPDATE users SET their_password=?, loggedin=? WHERE their_username=?")) { $insert_stmt->bind_param('sss', $their_password, $time, $user_email); $insert_stmt->execute(); $insert_stmt->close(); } // if query errors sends an email if ($mysqli->error) { try { throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno); } catch(Exception $e ) { $mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >"; $mess .= nl2br($e->getTraceAsString()); $contact_email = "webmaster@website.com"; $message_sub = "Mysqli Forgotten Password Query Error [UPASSWORD01]"; $hdrs = "From: " . $contact_email . "\r\n"; $hdrs .= "Reply-To: ". $contact_email . "\r\n"; $hdrs .= "MIME-Version: 1.0\r\n"; $hdrs .= "Content-Type: text/html; charset=UTF-8\r\n"; mail($contact_email, $message_sub, $mess, $hdrs); } header("refresh: 10; forgotpass.php"); die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.'); exit(); } $mysqli->close(); Query two (updates a reset log - to keep track on our user password resets): Code: [Select] // change status $resetstatus = "Successful Reset"; // connect to db for mysqli require('../db/db.php'); // inserts a new record $null = NULL; $insert_stmt = $mysqli->stmt_init(); if ($insert_stmt->prepare("INSERT INTO passwordresets VALUES (?, ?, ?, ?, ?)")) { $insert_stmt->bind_param('issss', $null, $user_email, $time, $userip, $resetstatus); $insert_stmt->execute(); $insert_stmt->close(); } // if query errors sends an email if ($mysqli->error) { try { throw new Exception("MySQL error $mysqli->error <br> Query:<br> $query", $mysqli->errno); } catch(Exception $e ) { $mess = "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br >"; $mess .= nl2br($e->getTraceAsString()); $contact_email = "webmaster@website.com"; $message_sub = "Mysqli Forgotten Password Query Error [UARESETLOG01]"; $hdrs = "From: " . $contact_email . "\r\n"; $hdrs .= "Reply-To: ". $contact_email . "\r\n"; $hdrs .= "MIME-Version: 1.0\r\n"; $hdrs .= "Content-Type: text/html; charset=UTF-8\r\n"; mail($contact_email, $message_sub, $mess, $hdrs); } header("refresh: 10; forgotpass.php"); die('ERROR: Unable to reset password. Please check you details and try again or report this error to us using our contact us form.<br><br>We will redirect you back to the forgotten password form in 10 seconds.'); exit(); } $mysqli->close(); Any ideas would be greatly appreciated. Thank you Not sure where to begin describing ths one?! On my website I have Articles. Beneath each Article, Users can leave one or more Comments. Next to each User's Comment, I have the following in the left margin... - Username - User Online Status - User Photo - User Location - User # of Posts At the top of my file, I have my PHP code and I start off by running a Prepared Statement looking for the Article the User is requesting in the URL. If the Article is found, I store the Results, and then run another Prepared Statement looking for Comments. If Comments are found, I store the Results. Then down in the HTML section, I display the Article and beneath it I loop through the Article's Comments like this... Code: [Select] // ******************************** // Display Comments on Article. * // ******************************** while (mysqli_stmt_fetch($stmt2)){ // Display User Info. * // Display User Comments. * } This seems to be working nicely, except that there is one last piece of User Info that is messing things up... I can get all of the User and Comments data in this query... Code: [Select] // ************************ // Build Comments Query. * // ************************ // Build query. $q2 = 'SELECT m.first_name, m.username, m.photo_name, m.photo_label, m.location, m.created_on, m.logged_in, m.last_activity, c.created_on, c.body, c.status FROM member AS m INNER JOIN comment AS c ON m.id = c.member_id WHERE c.status="Approved" AND c.article_id=? ORDER BY c.created_on'; However, in order to get the User's "Number of Posts" I need another query. And I'm not sure how to get the "Post Count" for each User and then mesh it back into what I currently have?! Does that make sense?! Also, it is conceivable that later I might add additional User Info that comes from yet another Query/Source. (If I was using OOP and PDO and had a clear separation of "Presentation" from "Business Logic" I'm sure would be easier, but for now I need to work with the structure I have since any of those three things are way beyond the scope of this question and my current abilities!!!!) Thanks, Debbie I'm working on a Stats page that will need to run multiple queries to access the information needed to dislpay on the page. Rather than running 30+ queries to get the user's Stats, is there a better method? Any tips that you suggest I further research? Thanks 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. 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 Main script gets class information from a database and prints them so long as the class start date or end date is after today (actually includes today).
Main script calls "instructors.php". It queries another database based on the instructor name ($chef) and then prints the bio information for that instructor.
"instructors.php" works fine on it's own, when I add "$chef = "Chef Name" ("Chef Name" is in the Instructors database). When it's called from the main script, nothing shows up in that area - even though "Chef Name" is in the database. All of the other data is printed fine, just not anything from instructors.php. I verified that it's actually including the file, as I can add "echo "test";" to the top of instructors.php and it prints fine in the main script.
Any ideas of what I'm missing?
Main Script
<?php // Get required login info include "/path/to/login/info/file.php"; // Get required login info - changed for this post. $db = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info if($db->connect_errno > 0){ die('Unable to connect to database [' . $db->connect_error . ']'); } unset($username);// put these variables back to null unset($password);// put these variables back to null unset($database);// put these variables back to null //query db $sql = <<<SQL SELECT * FROM `ft_form_7` WHERE DATE(class_start_date) >= CURDATE() OR DATE(class_end_date) >= CURDATE() ORDER BY class_start_date ASC SQL; if(!$result = $db->query($sql)){ // if there is an error in running the query, show error message. die('There was an error running the query [' . $db->error . ']'); } while($row = $result->fetch_assoc()){ // Get start date information $start_date = $row['class_start_date']; // Get event_start_date for conversion and call it $start_date $start_date_formatted = date("l M d, Y", strtotime($start_date)); // Convert start_date $end_date = $row['class_end_date']; // Get event_end_date for conversion and call it $start_date $end_date_formatted = date("M d, Y", strtotime($end_date)); // Convert start_date // Get time information. $start_time = $row['class_start_time']; // Get event_start_time for conversion and call it $start_time $start_time_formatted = date("h:i A", strtotime($start_time)); // Convert start_time $end_time = $row['class_end_time']; // Get event_end_time for conversion and call it $end_time $end_time_formatted = date("h:i A", strtotime($end_time)); // Convert end_time // echo information... echo "<h2>" , $row['class_name'],"</h2>" ; // echo event name echo "<p><strong>",$start_date_formatted; // echo the start date if (empty($start_time)) { echo ''; } else { echo " (", $start_time; } // echo start time if (empty($end_date)) { echo ''; } else { echo " -","<br />", $end_date_formatted; } // echo end date if (empty($end_time)) { echo ')'; } else { echo " - ", $end_time, ")"; } // echo end time // if there is no start time, echo nothing. (otherwise it seems to echo 4pm). If it does contain a time, echo the time. echo "</strong><br />"; $chef = $row['Instructor']; global $chef; if ($chef != NULL) { require ('instructors.php'); } echo $row['class_description'], "<br />"; echo "<strong>" , $row['type'], " - Cost: $",$row['cost'] , " - #" , $row['course_number'] , "</strong><br />" , "</p>"; // echo class type and cost } $db->close(); $result->free(); ?>instructors.php <?php include "/path/to/login/info/file.php"; // Get required login info - changed for this post. $db_instructors = new mysqli('localhost', $username, $password, $database); // Connect to DB using required login info if($db_instructors->connect_errno > 0){ die('Unable to connect to database [' . $db_instructors->connect_error . ']'); } unset($username);// put these variables back to null unset($password);// put these variables back to null unset($database);// put these variables back to null //query db $sql_instructors = <<<SQL SELECT * FROM ft_form_8 WHERE chef_name = '$chef' SQL; if(!$result_instructors = $db_instructors->query($sql_instructors)) { // if there is an error in running the query, show error message. die('There was an error running the query [' . $db_instructors->error . ']'); } while($row_instructors = $result_instructors->fetch_assoc()) { $chef_full_name = $row_instructors['chef_name']; $chef_id = $row_instructors['submission_id']; $full_bio = $row_instructors['full_bio']; echo "<a href=\"#\" class=\"clickme\">" , $chef_full_name , "</a>"; echo "<div class=\"box\">"; echo $full_bio , "</div>"; } $db_instructors->close(); $result_instructors->free(); ?> I'm using paypal PDT, so when the user has made payment he gets directed back to my site, on that page it executes a special sql query. I noticed I can refresh the page to make duplicate sql queries. How can I avoid this?? Hey guys - me again! I have a discount box, where i wish to check two "if" queries. These are i) the 'uniquecode' and ii) the 'uses' - so a code can only be used a set amount of times. 1) How do i state that question in php, bearing in mind the "if uses > 0" needs to relate to the same row as the unique code entered? 2) Also, i have stored the session price in $_SESSION['sessionprice'] - is this the best way to do this, and if not how should i store the current price. 3) Lastly, and how do I do the php mathematics of "$_SESSION['sessionprice'] minus the discount" - again bearing in mind the "discount" needs to relate to the same row as the unique code entered? (as different codes will have different discounts). This is how far i've gotten: Code: [Select] <?php //connection settings bla bla bla $uniquecode = $_POST['discountcode']; mysql_connect($localhost,$username,$password); @mysql_select_db($database) or die( "Unable to select database"); $discountcodecheck = mysql_query("SELECT uniquecode FROM discounttable WHERE uniquecode = '".$uniquecode."'"); $remainingusescheck = mysql_query("SELECT uses FROM discounttable WHERE uniquecode = '".$uniquecode."'"); if (mysql_num_rows($discountcodecheck) > 0) and if (mysql_num_rows($remainingusescheck) > 0) // <-- this "and if" doesn't work { // discount calculation: // session price minus the unique code's corresponding discount // update $_session['discountammount'] } else { ?><script type="text/javascript"> alert("Discount Code Entered Is Either Not Valid Or Has Been Previously Used."); history.back(); </script><?php } // rest of code and close connection ?> Any help or comments will, as always be politely welcomed and appreciated Cheers, Tom. Hello all, I've tried several ways to calculate a commission in the multi level marketing script, but it all ended up with nothing, recursive functions, nested sets nothing worked, and i'm running out of time to keep trying I've made multiple queries on the same table, and it's working, but only the first 3 queries, not more, I tried to perform it 4 times (although I need the queries to be performed 13 times) but still only 3 times example: A recruited B and C, and B recruited D, and D recruited E.... what's supposed to happen is A takes a commission on B, C, D, and E but now it only takes commission on the first three, and no commission for anyone comes after that, and the same for B, C, D, and E each takes commission on only 3 members down the line, and no more here is the queries Code: [Select] <? $result = mysql_query("SELECT * FROM users"); echo "<table width='589' border='1'> <tr> <th>ID</th> <th>Name</th> <th>National ID</th> <th>Commission</th> </tr>"; while($row = mysql_fetch_array($result)) { $query = mysql_query("SELECT * from users where recruiteris = '".$row['id']."'"); $num_rows=mysql_num_rows($query); $id1 = $row['id']; $commission = $num_rows; $_SESSION['id1'] = $id1; echo "<tr>"; while ($roww = mysql_fetch_array($query)) { $querry=mysql_query("select * from users where recruiteris = '".$roww['id']."'"); $num_rowss = mysql_num_rows($querry); while ($rowws= mysql_fetch_array($querry)) { $var3= '10'; $querrys=mysql_query("select * from users where recruiteris = '".$rowws['id']."'"); $num_rowsss = mysql_num_rows($querrys); while ($rowwss= mysql_fetch_array($querrys)) { $querryss=mysql_query("select * from users where recruiteris = '".$rowwss['id']."'"); $num_rowssss = mysql_num_rows($querryss); while ($rowwsss= mysql_fetch_array($querryss)) { $querryr=mysql_query("select * from users where recruiteris = '".$rowwsss['id']."'"); $num_rowssr = mysql_num_rows($querryr); } } } } $total= ($commission + $num_rowws + $num_rowwss + $num_rowwsss + $num_rowss + $num_rowssr) * $var3; echo "<td><a href=\"user.php?id=".$row['id']."\">".$row['id']."</a></td>"; echo "<td>" . $row['fname'] .''. $row['lname'] ." </td>"; echo"<td> ". $row['nid'] ." </td>"; echo "<td>".$total."</td> </tr>"; } echo "</table>"; ?> ive had these queries working okay until i checked on it today, it's not working anymore.. these are the errors::: Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 105 Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in C:\wamp\www\arrastre\add.php on line 117 if(isset($_POST['add'])){ if($billnmbr=="" or $orno=="" or $payor=="" or $arrastre=="" or $wharfage=="" or $total=="" or $date=="" or $tcl==""){ echo "At least one field was left blank."; } else{ $query = mysql_query("select * from `arrastre` WHERE `billnmbr`='$billnmbr'"); /*****************this is my line 105************/ $count = mysql_num_rows($query); if($count==1){ echo "This billnumber is already in the database."; } else{ $bll = strtoupper($billnmbr); $payr = strtoupper($payor); $query="insert into `arrastre` (`billnmbr`, `orno`, `payor`, `arrastre`, `wharfage`, `total`, `date`, `tcl`) values ('$bll', '$orno', '$payr', '$arrastre', '$wharfage', '$total', '$date', '$tcl')"; $result=mysql_query($query); $query = mysql_query("select * from `arrastre` where `billnmbr`= '$billnmbr'", $link); /************and this is my line 117*************/ $count = mysql_num_rows($query); if($count==1){ echo "last bill number added: ".$billnmbr; } } Thank you very much for your time and have a nice day. noob question: I have following two queries I'd like to combined into one - how is this done? $temp = @mysql_query("SELECT * FROM purchased_leads WHERE leadID = '{$_REQUEST[leadid]}'"); "SELECT refundNotes FROM leads WHERE leadID = '{$_REQUEST[leadid]}'" Good day everyone. I go by the nickname Sbosh and I am a newbie in PHP. I am currently learning php using video tutorials. I need help with regards to php queries. I have the following code: "SELECT 'food', 'calories' FROM 'diet' ORDER BY 'id'" which is supposed to display content from a table inside phpmyadmin which i created manually using phpmyadmin. But It gives an error saying i must check the MariaDB version on how to right this query, something like that. Please assist on the proper way of writing this code. Again I am a newbie in PHP and just starting to learn, so your help will be highly appreciated. // Check for this userID if exsist in cp_credits $sql = "SELECT * FROM `cp_credits` WHERE account_id = $userid"; // Execute Query $r = mysqli_query($dbc, $sql); // If there are result in $r if($r){ // Print a message indicating success or not: while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC)){ $user_balance = $row['balance']; $user_last_donation_date = $row['last_donation_date']; } } else{ $user_balance = "nothing"; $user_last_donation_date = "nothing"; } but either of my else statement does not work. Whats wrong with this? This must be easy enough, but I'm a noob so go easy on me, please :-)
I have two pieces of code and they're working well on their own. All I need is to put these two conditions into one query.
The first one:
<?php function childtheme_cat_limited_blog( $query ) { if ( $query->is_home() && $query->is_main_query() ) { $query->set( 'events_categories', 'mycategory1' ); } } add_action( 'pre_get_posts', 'childtheme_cat_limited_blog' ); ?>And the second: <?php function childtheme_cat_limited_blog( $query ) { if ( $query->is_home() && $query->is_main_query() ) { $query->set( 'category_name', 'mycategory2' ); } } add_action( 'pre_get_posts', 'childtheme_cat_limited_blog' ); ?>I'm sure it's a piece a cake for you guys, but I can't figure it out for the life of me, so any help would be greatly appreciated! Thank you! (My goal is here to display posts from two categories on a homepage of a Wordpress site, but to complicate things the two categories are from two different post types.) UPDATE: If I remove lastedit = NOW() from the query, and just leave SET content = $var, it works. :/ If already double checked to see if a user would make it to this point in the code, and they do. And the variable $pid IS assigned a value. I've also done a quick check and changed the UPDATE queries to SELECT queries, and then echoed out the number of rows and it returned a successful result. Yet, people still can't seem to have their posts updated...why? My code: //if it's a thread, update the thread, if it's a post, update the post if($type == 2) { //update thread mysql_query("UPDATE threads SET lastedit = NOW() AND content = '{$content}' WHERE id = {$id}") or die(mysql_error()); //send them to their post redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page); } else { //update post mysql_query("UPDATE posts SET lastedit = NOW() AND content = '{$content}' WHERE id = {$pid}") or die(mysql_error()); //send them to their post redirect('viewthread.php?forum='. $forum_id .'&id='. $id .'&page='. $page .'&post='. $post); } Hi there everyone! I've got a problem combining two queries. The situation is this. I've got a list of links and I want to allow the user to get a result list of all, only unread, all in a particular category and then only unread in a particular category. My query for all: /* No filtering and no unread */ $q_urls = "SELECT * FROM shortenified_urls WHERE is_social = '1' AND active = '1' ORDER BY date_added DESC"; My query for in a particular category: /* All links in a particular category */ $q_urls = " SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view OR EXISTS ( SELECT link_id FROM linkcats WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ;My query for Unread in all cats: /* Unread only for all categories */ $q_urls = "SELECT * FROM shortenified_urls WHERE shortenified_urls.date_added > $vu_mark_as_read AND NOT EXISTS ( SELECT lid FROM linkviews WHERE lid = shortenified_urls.id AND uid = $viewing_user_id ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ; These seem to be working well. It's the next one that isn't working for me. I tried to combine the "All in a particular category" and "Unread in all categories" using the two above as starting points. The result, however, seems to be that I'm getting all in a particular category. It seems to be ignoring the unread status. /* Unread links in a particular category */ $q_urls = " SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read OR EXISTS ( SELECT link_id FROM linkcats WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view ) AND NOT EXISTS ( SELECT lid FROM linkviews WHERE lid = shortenified_urls.id AND uid = $viewing_user_id ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ;I'm not smart enough to know why it's not working, but it seems to me like I need to somehow bracket the first two parts containing the category retrieval to make this work right. The only problem is I don't know how to do that. Any help on how to get this to work would be greatly appreciated! Here is the original query: Code: [Select] $query_extract = mysql_query("SELECT downloads,date,title,username,views,id FROM skins ORDER BY id DESC LIMIT 100"); Is it possible to also grab the row username from the table users, without making multiple queries? |