rvdveen27 Posted June 15, 2015 Share Posted June 15, 2015 Hello all, This is probably going to be a really simple question, but for a page I'm making I cannot figure out how to put out the date of the convoy. I'm getting the error: Notice: Undefined index: date in/home/emerald/domains/emeraldimmersiontrucking.com/public_html/convoy.php on line 84 Here's the code: $query = " SELECT u.username ,cv.date as 'date' FROM convoy cv, convoysignup cvsu INNER JOIN users u ON u.id = cvsu.name "; try { $stmt = $db->prepare($query); $result = $stmt->execute(); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $rows = $stmt->fetchAll(); $count = $stmt->rowcount(); ?> <center><img src="images/underconstruction.jpg" width="15%"><br> Information shown on this page might be not properly shown, incorrect or incomplete.<br> </center> <br> <br> <center><h2>The next convoy will be at:</h2><br> <h1><?php echo $rows['date']; ?> GMT +1</h1><br> </center> <br> <br> <br> <div class="container" style="width:450px;"> <h1>Sign ups</h1><br> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th>#</th> <th>Name</th> </tr> </thead> <?php $rank = 0; foreach($rows as $row): ++$rank; ?> <tbody> <tr> <td><?php echo $rank; ?></td> <td><?php echo $row['username']; ?></td> </tr> </tbody> <?php endforeach; ?> </table> </div> <br> <br> Hoping someone can help me in solving this issue, thanks in advance. Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted June 15, 2015 Share Posted June 15, 2015 you have used single quotes in your query to define the alias date. these should be backticks ( ` ), not single quotes ( ' ). Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 15, 2015 Share Posted June 15, 2015 $rows contains a multidimensional array of results from your query. Each row being a separate item in the array, forexample $row[0] will contain the data for the first row, $rows[1] will contain the data for the second row and so on. If the date is the same throughout the results then you would use $rows[0]['date'] in place of $rows['date'] 1 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted June 15, 2015 Share Posted June 15, 2015 $rows contains a multidimensional array of results from your query. Each row being a separate item in the array, forexample $row[0] will contain the data for the first row, $rows[1] will contain the data for the second row and so on. If the date is the same throughout the results then you would use $rows[0]['date'] in place of $rows['date'] lol, I seen the foreach $rows down the bottom of the post and completely ignored the fact that wasn't inside it! Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 15, 2015 Author Share Posted June 15, 2015 $rows contains a multidimensional array of results from your query. Each row being a separate item in the array, forexample $row[0] will contain the data for the first row, $rows[1] will contain the data for the second row and so on. If the date is the same throughout the results then you would use $rows[0]['date'] in place of $rows['date'] Cool, that did indeed fix it. Thanks a lot I have one more question. I'm trying to get it so that users can sign up with the press of a button. When they press the button, their username should automatically be entered into the convoysignup table. My code seems to be working (no errors or anything), except nothing gets put into the database. $query = " SELECT u.username ,cv.date as 'date' FROM convoy cv, convoysignup cvsu INNER JOIN users u ON u.id = cvsu.name "; try { $stmt = $db->prepare($query); $result = $stmt->execute(); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $rows = $stmt->fetchAll(); $count = $stmt->rowcount(); ?> <center><img src="images/underconstruction.jpg" width="15%"><br> Information shown on this page might be not properly shown, incorrect or incomplete.<br> </center> <br> <br> <center><h2>The next convoy will be at:</h2><br> <h1><?php echo $rows[0]['date']; ?> GMT +1</h1><br> </center> <br> <br> <?php if(!empty($_POST)) { /* if(!is_numeric($_POST['price'])) { header("Location: errorjob.php?id=002"); exit; } if(!is_numeric($_POST['distance'])) { header("Location: errorjob.php?id=001"); exit; } if(!is_numeric($_POST['costs'])) { header("Location: errorjob.php?id=003"); exit; }*/ $query = " INSERT INTO convoysignup ( username ) VALUES ( :username ) "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } header("Location: myjobs.php"); exit; } ?> <div class="container" style="width:450px;"> <form class="form-signin" role="form" action="convoy.php" method="post"> <button class="btn btn-lg btn-primary btn-block" type="submit">Sign up for this convoy</button> </form> <br> <br> <br> <h1>Sign ups</h1><br> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th width="7%">#</th> <th>Name</th> </tr> </thead> <?php $rank = 0; foreach($rows as $row): ++$rank; ?> <tbody> <tr> <td><?php echo $rank; ?></td> <td><?php echo $row['username']; ?></td> </tr> </tbody> <?php endforeach; ?> </table> </div> Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 15, 2015 Share Posted June 15, 2015 (edited) Its not working because you only go to add the user when $_POST is not empty if(!empty($_POST)) In your form <form class="form-signin" role="form" action="convoy.php" method="post"> <button class="btn btn-lg btn-primary btn-block" type="submit">Sign up for this convoy</button> </form> There is nothing to submit, so no data will be in $_POST. This is why no user is being entered into your database. I recommend adding a name to your submit button, eg <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> Then change if(!empty($_POST)) to if(isset($_POST['addConvoyUser'])) Also when using sessions make sure you have called session_start() at the top of your script Edited June 15, 2015 by Ch0cu3r 1 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 15, 2015 Author Share Posted June 15, 2015 Thanks, that worked perfectly. I'm now trying to show a message when the person has already signed up for the convoy instead of the signup button. So far I got the code shown below, but the "die" causes it to end the whole page after that, thus not showing the user who signed up etc. Is there a way that I can just show the message "You have already signed up for the convoy" instead of the button, while still showing the rest of the page? <?php $query = " SELECT 1 FROM convoysignup WHERE username = :username "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $row = $stmt->fetch(); if($row) { die("You have already signed up for the convoy."); } if(isset($_POST['addConvoyUser'])) { /* if(!is_numeric($_POST['price'])) { header("Location: errorjob.php?id=002"); exit; } if(!is_numeric($_POST['distance'])) { header("Location: errorjob.php?id=001"); exit; } if(!is_numeric($_POST['costs'])) { header("Location: errorjob.php?id=003"); exit; }*/ $query = " INSERT INTO convoysignup ( username ) VALUES ( :username ) "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } header("Location: convoy.php"); exit; } ?> <div class="container" style="width:450px;"> <form class="form-signin" role="form" action="convoy.php" method="post"> <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> </form> Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 15, 2015 Share Posted June 15, 2015 Dont use die use echo See the documentation on die http://php.net/die Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 15, 2015 Author Share Posted June 15, 2015 Dont use die use echo See the documentation on die http://php.net/die That does indeed show the message, but it does not replace the button and it also doesn't stop the query from being ran again, thus allowing multiple rows for the same user. (replacing the button is optional, would be great if that could be done, but not necessary. Stopping the query from running again if there's already a row in the table from that user is necessary.) Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 15, 2015 Share Posted June 15, 2015 (edited) Before querying the table to see if the user is in the convoy define a variable such as this $inConvoy = false; Then when you check your query has returned a result. Set that variable to true if($result->rowCount() > 0) { $inConvoy = true; } Change the if statement for checking if $_POST['addConvoyUser'] exists to if(!$inConvoy && if(isset($_POST['addConvoyUser'])) Then wrap your sign up form in a if statement so it only shows the form if $inConvoy is false <?php if(!$inConvoy)): ?> <form class="form-signin" role="form" action="convoy.php" method="post"> <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> </form> <?php else: ?> You have already signed up for the convoy. <?php endif; ?> Edited June 15, 2015 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 15, 2015 Author Share Posted June 15, 2015 This: <?php $inconvoy = false; $query = " SELECT 1 FROM convoysignup WHERE username = :username "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $count = $stmt->rowcount(); if($count->rowcount() > 0) { $inconvoy = true; } is giving me: Fatal error: Call to a member function rowcount() on integer in/home/emerald/domains/emeraldimmersiontrucking.com/public_html/convoy.php on line 118 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted June 16, 2015 Share Posted June 16, 2015 well you have already set $count to the rowcount() on line 116, and now on line 118 you are sking for the rowcount() of $count... Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 16, 2015 Author Share Posted June 16, 2015 (edited) Yes but if I take that out and leave it like this: <?php $inconvoy = false; $query = " SELECT 1 FROM convoysignup WHERE username = :username "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } if($result->rowcount() > 0) { $inconvoy = true; } $row = $stmt->fetch(); I get: Fatal error: Call to a member function rowcount() on boolean in /home/emerald/domains/emeraldimmersiontrucking.com/public_html/convoy.php on line 128 Edited June 16, 2015 by rvdveen27 Quote Link to comment Share on other sites More sharing options...
Barand Posted June 16, 2015 Share Posted June 16, 2015 It's time you read this http://php.net/manual/en/pdostatement.execute.php Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 16, 2015 Share Posted June 16, 2015 Sorry, $result->rowCount() should of been $stmt->rowCount() Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 16, 2015 Author Share Posted June 16, 2015 I've got the page set up like this right now and it works perfectly fine. Thanks a lot for all the help. Code: <?php $inconvoy = false; $query = " SELECT 1 FROM convoysignup WHERE username = :username "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } if($stmt->rowcount() > 0) { $inconvoy = true; } $row = $stmt->fetch(); ?> <?php if(!$inconvoy): ?> <form class="form-signin" role="form" action="convoy.php" method="post"> <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> </form> <?php else: ?> <font color="green" size="5"><b>You have already signed up for the convoy.</b></font> <?php endif; ?> </center> <?php if(isset($_POST['addConvoyUser'])) { /* if(!is_numeric($_POST['price'])) { header("Location: errorjob.php?id=002"); exit; } if(!is_numeric($_POST['distance'])) { header("Location: errorjob.php?id=001"); exit; } if(!is_numeric($_POST['costs'])) { header("Location: errorjob.php?id=003"); exit; }*/ $query = " INSERT INTO convoysignup( username ) VALUES ( :username ) "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } header("Location: convoy.php"); exit; } ?> I did however not include the given below in the code: Change the if statement for checking if $_POST['addConvoyUser'] exists to if(!$inConvoy && if(isset($_POST['addConvoyUser'])) Everytime I do replace/add that line into the code, it breaks the code. I already figured the code given misses a ")", but adding that still doesn't solve the breaking (blank page) of the page. Should I worry about figuring out why that is not working even though the page is currently working fine without it? Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 17, 2015 Share Posted June 17, 2015 Sorry that line should be if(!$inConvoy && isset($_POST['addConvoyUser'])) Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 17, 2015 Author Share Posted June 17, 2015 Works just fine now. I have one more question regarding the INSERT MySQL Query. When a player pressed the button, the query should insert their username into the database (which it already does), but it should also get the latest "id" from the table "convoy" and add that to the user's signup for the field "convoyid" in the "convoysignup" table. Now I went and looked this up. I assumed it would go via a INSERT.. SELECT (inner join?) query, but I have no idea how this could exactly be done. I'm hoping someone can explain me how to get data from one table and insert it in another, all within the same query. Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 22, 2015 Author Share Posted June 22, 2015 Sorry to bump this but anyone able to help me with formatting this query? So far I haven't been able to get it working. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 22, 2015 Share Posted June 22, 2015 (edited) assumed it would go via a INSERT.. SELECT (inner join?) query, but I have no idea how this could exactly be done. If the id column in the convory table is auto_increment then you can use mysqli->insert_id. Edited June 22, 2015 by Ch0cu3r Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 23, 2015 Author Share Posted June 23, 2015 If the id column in the convory table is auto_increment then you can use mysqli->insert_id. I've read this through and it says: If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.[/size] Assuming the OR bit in that sentence, I assume it could work with my SELECT query that happens before, since the table of that SELECT query has an AUTO_INCREMENT. But then reading further through the page it seems this function is only used in an echo or a printf function, not in a query itself? To clarify what I meant I've added some pictures below: When a convoy is created, it's assigned an "id", a "date" and a "comment". On the convoy page, it will look up the entry with the highest ID, and show the "date" and "comment" of that entry. ^^Convoy table ^^ ^^ What's shown on the webpage ^^ $query = " SELECT u.username ,cv.date as 'date' ,cv.comment as 'comment' FROM convoy cv, convoysignup cvsu INNER JOIN users u ON u.id = cvsu.username "; try { $stmt = $db->prepare($query); $result = $stmt->execute(); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $rows = $stmt->fetchAll(); $count = $stmt->rowcount(); ?> <center><img src="images/underconstruction.jpg" width="15%"><br> Information shown on this page might be not properly shown, incorrect or incomplete.<br> </center> <br> <br> <div class="container" style="width:550px;"> <center> <h2>The next convoy will be at:</h2><br> <div class="panel panel-default"> <div class="panel-heading"> <font size="5"><?php echo $rows[0]['date']; ?> GMT +1</font><br> </div> <div class="panel-body"> <font size="3"><?php echo $rows[0]['comment']; ?></font><br> <br> Please read the <a href="inforules.php">Information & Rules</a> page for the convoy rules before departure.<br> </div> </div> </center> ^^ Code for stating when the next convoy is etc ^^ Then when a user presses the sign up for the convoy button, it should insert their "username" along with the "convoyid" (which is the "id" from the convoy table) into the "convoysignup table. In the image below you can see 2 entries where the "convoyid" field is set correctly, this was done manual. ^^ convoysignup table entries ^^ Which should result in showing the correct amount of entries for each convoy on the website, like this: Here's my code for showing the previous convoys, including the insert query for adding a user to the "convoysignup" table, which currently does not include the "convoyid" which is supposed to always be equal to the "id" in the "convoy" table of the convoy which is currently shown on the webpage (the one with the highest "id"). <?php if(!$inconvoy && isset($_POST['addConvoyUser'])) { $query = " INSERT INTO convoysignup ( username ) VALUES ( :username ) "; $query_params = array( ':username' => $_SESSION['userid'] ); try { $stmt = $db->prepare($query); $result = $stmt->execute($query_params); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } header("Location: convoy.php"); exit; } ?> <br> <br> <br> <div class="container" style="width:400px;"> <h1>Sign ups</h1><br> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th width="8%">#</th> <th>Name</th> </tr> </thead> <tbody> <?php $rank = 0; foreach($rows as $row): ++$rank; ?> <tr> <td><?php echo $rank; ?></td> <td><?php echo $row['username']; ?></td> </tr> <?php endforeach; ?> </tbody> </table> </div> <br> <br> </div> <?php $query = " SELECT cv.id as id ,cv.date as date ,COUNT(username) as signups FROM convoysignup cvsu, convoy cv WHERE cv.id = cvsu.convoyid "; try { $stmt = $db->prepare($query); $result = $stmt->execute(); } catch(PDOException $ex) { die("Failed to run query: " . $ex->getMessage()); } $rows = $stmt->fetchAll(); $count = $stmt->rowcount(); ?> <br> <br> <br> <div class="container"> <h1>Previous Convoys</h1> <div class="table-responsive"> <table class="table table-striped"> <thead> <tr> <th>#</th> <th>Date</th> <th>Sign ups</th> </tr> </thead> <?php foreach($rows as $row): ; ?> <tbody> <tr> <td><?php echo $row['id']; ?></td> <td><?php echo $row['date']; ?></td> <td><?php echo $row['signups']; ?></td> </tr> </tbody> <?php endforeach; ?> </table> </div> </div> I hope this gives a somewhat clearer explanation of what I meant. Sorry if my previous explanation was somewhat unclear. Quote Link to comment Share on other sites More sharing options...
Ch0cu3r Posted June 23, 2015 Share Posted June 23, 2015 Then you need to pass the convoy id when the user clicks the sign up button First you need to change the query which gets convoy date and the users signed up to convoy to also return the convoy id $query = " SELECT u.username, cv.date as 'date', cv.id as convoy_id # get the convoy id FROM convoy cv, convoysignup cvsu INNER JOIN users u ON u.id = cvsu.name "; Then in the signup form, add the convoy id as hidden input field <form class="form-signin" role="form" action="convoy.php" method="post"> <input type="hidden" name="convoy_id" value="<?php echo $row['convoy_id']; ?>" /> <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> </form> Then when adding the user to the convoy retrieve the convory id from $_POST $query = " INSERT INTO convoysignup ( username, convoyid ) VALUES ( :username, :convoyid )"; $query_params = array( ':username' => $_SESSION['userid'], ':convoyid' => intval($_POST['convoy_id']) ); 1 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted June 25, 2015 Author Share Posted June 25, 2015 It works perfectly! Thanks so much for this. Not only for the solution to my question but also for explaining how to do it. Learned something new once again I came across one more thing that I have a question about. As the code currently stands, it's possible to sign up even if the date of the convoy has already passed. So I was thinking of hiding the sign up button if the date of the convoy has passed. I'm thinking I should fit such a thing in this bit of code: <?php if(!$inconvoy): ?> <form class="form-signin" role="form" action="convoy.php" method="post"> <input type="hidden" name="convoyid" value="<?php echo $rows[0]['convoyid']; ?>" /> <button class="btn btn-lg btn-primary btn-block" name="addConvoyUser" type="submit">Sign up for this convoy</button> </form> <?php else: ?> <center> <font color="green" size="5"><b>You have signed up for the convoy.</b></font> </center> <?php endif; ?> Now before I used DATE_FORMAT(CURDATE(), '%Y-%m') on another page. Would something like this also be able to be used in that PHP "if/else" section? I'm thinking something along the lines: IF DATE_FORMAT(CURDATE(), '%y-%m-%d-%h-%m') >= convoydate THEN hide convoy signup button. Is it possible to use such a function in a PHP if section? Quote Link to comment Share on other sites More sharing options...
Barand Posted June 25, 2015 Share Posted June 25, 2015 (edited) Perhaps if (time() >= strtotime($convoydate) ) Edited June 25, 2015 by Barand 1 Quote Link to comment Share on other sites More sharing options...
rvdveen27 Posted July 5, 2015 Author Share Posted July 5, 2015 Thanks once again Barand, it works just fine Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.