Help pulling data from previous page

[lukep11a]

Hi, I have a table named 'privateleague' with fields 'privateleagueid', 'privateleaguename', 'privateleaguepasscode' and 'userid'. It is so users can create private leagues to invite their friends to, the form currently works and submits the data, and on the following page details of the league they have just created is displayed and they are then given the chance to email friends they want to invite. The way I have done it, the userid is submitted to the table and then on the following page the details are found by looking up the userid. My problem is that if they then create another private league details of both private leagues are displayed rather than the one they just created. Here is my form code:

 

<form id="form1" name="privateleague" method="post" action="privateleague.php">
      <input name="userid" type="hidden" value="<?= $_SESSION['userid'] ?>">
      <p class="normal">Private League Name: <input name="privateleaguename" type="text" size="30" maxlength="30" /></p>
      <p class="normal">Passcode: <input name="privateleaguepasscode" type="text" size="30" maxlength="30" /></p>
      <input type="submit" value="Create Private League">
      </form>

 

Here is my processing code:

<?php

require_once "header.php";

$privateleaguename = $_POST['privateleaguename'];
$privateleaguepasscode = $_POST['privateleaguepasscode'];
$userid = $_POST['userid'];

$query="INSERT INTO privateleague (privateleaguename, privateleaguepasscode, userid)VALUES ('".$privateleaguename."','".$privateleaguepasscode."','".$userid."')";
mysql_query($query) or die ('Error updating database');

?>

 

And here is the code that displays details of the private league just created:

 

<?php

	$query = "SELECT * FROM privateleague WHERE userid = '{$_SESSION['userid']}'";
	$result = mysql_query($query) or die(mysql_error());

	while($row = mysql_fetch_assoc($result))
	{
		echo $row['privateleaguename'];
		echo "<br \>";
		echo $row['privateleaguepasscode'];
      	}

	?>

 

Does anyone know how I can get the details of the league just created, rather than details of every league created by that user?

[QuickOldCar]

if you only want one limit the results

 

<?php

	$query = "SELECT * FROM privateleague WHERE userid = '{$_SESSION['userid']}' LIMIT 0,1";
	$result = mysql_query($query) or die(mysql_error());

	while($row = mysql_fetch_assoc($result))
	{
		echo $row['privateleaguename'];
		echo "<br \>";
		echo $row['privateleaguepasscode'];
      	}

	?>

 

if need to use a specific order for latest result... see here

http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

[AyKay47]

if you only want to display one row, you can probably get away with simply taking away the while loop...mysql should grab the most current row where userid = $_SESSION['userid']

 

	$query = "SELECT * FROM privateleague WHERE userid = '{$_SESSION['userid']}'";
	$result = mysql_query($query) or die(mysql_error());

	$row = mysql_fetch_assoc($result);

		print $row['privateleaguename'] . "<br />";
		print $row['privateleaguepasscode'];
      	

[DavidAM]

This is a dangerous habit to get into. In an RDBMS (Relational Database Management System) there is no concept of "current row" or "most recent" or even an order (other than an using the ORDER BY clause). If no ORDER BY is provided, the server can return rows in any order it chooses. Since different query plans (based on JOINs or index size) can result in different internal sorts; as your database grows, a query that returns rows in one sequence today might return them in a completely different sequence next week (or year).

 

The proper way to return the "most recent row" is to determine what "most recent" means. If you want the most recent by date, add a CreateTime column, set it to NOW() (on insert and/or update) and ORDER BY that column DESCENDING. If you want the "last row inserted", you need to create an ID column as AUTO INCREMENT, capture the "Last Insert ID" (using the LAST_INSERT_ID() function on the database server or the mysql_insert_id PHP function) and use that value to retrieve the row. Note: The last insert id is based on the connection in use. So, different users WILL get their last insert ID NOT one from a different user.

 

And yes, when selecting a single row, you do not need to use a while loop. You can just fetch the first row returned (after verifying that a row was actually returned). But do NOT select a bunch of rows if you only need one. The database server has to process the rows and WILL SEND THEM TO PHP, and PHP (internally) will have to capture and buffer the ROWS. This is a lot of resources (time, memory, cpu, bandwidth) WASTED on data you never intend to look at. Use "LIMIT 1", or a where clause providing a unique ID, when you only want one row.

[lukep11a]

Thanks to you all, got it sorted and much more efficiently with all your help!