Echo data

[rvdveen27]

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.

[Muddy_Funster]

you have used single quotes in your query to define the alias date.  these should be backticks ( ` ), not single quotes ( ' ).

[Ch0cu3r]

$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']

[Muddy_Funster]

$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! 

[rvdveen27]

$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>

[Ch0cu3r]

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

[rvdveen27]

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>

[Ch0cu3r]

Dont use die use echo

 

See the documentation on die http://php.net/die

[rvdveen27]

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.)

[Ch0cu3r]

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

[rvdveen27]

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

[Muddy_Funster]

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...

[rvdveen27]

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

[Barand]

It's time you read this

 

http://php.net/manual/en/pdostatement.execute.php

[Ch0cu3r]

Sorry, $result->rowCount() should of been $stmt->rowCount()

[rvdveen27]

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? 

[Ch0cu3r]

Sorry that line should be

if(!$inConvoy && isset($_POST['addConvoyUser'])) 

[rvdveen27]

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.

[rvdveen27]

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.

[Ch0cu3r]

 

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

[rvdveen27]

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.

[Ch0cu3r]

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'])
   );

[rvdveen27]

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? 

[Barand]

Perhaps

if (time() >= strtotime($convoydate) )

Edited June 25, 2015 by Barand

[rvdveen27]

Thanks once again Barand, it works just fine