No errors but still can't pull results from table! 1

[rogwilco]

I have a MySQL table called tennis where one of the fields is a primary key called "id".

I also have a form which allows the user to add thereself to that table IF they are not already on that table, i.e. if there user id isn't already present. I am having trouble getting the check to work. I am almost 100% sure that my logic in the way I have done it is correct but I pretty sure I am making a newbie mistake somewhere along the way. Here's the part of code where it all goes on:


PHP Code:
<?php
session_name('YourVisitID');
ini_set('session.use_cookies', 0);
session_start();
?>
<?php

$self = $_SERVER['PHP_SELF'];
$username = $_POST['username'];
$id = $_SESSION['id'];
$leaderboard = $_POST['leaderboard'];
?>
Please select a leaderboard to add
yourself onto.
<?php
echo("$id"); #DEBUG result=2
if((!$leaderboard))
{
$form ="<form action=\"$self\" method=\"post\">";
$form.="<select name=\"leaderboard\" size=\"1\" value=\"$leaderboard\">";
$form.="<option value =\"squash\">Squash</option>";
$form.="<option value =\"badminton\">Badminton</option>";
$form.="<option value =\"tennis\">Tennis</option>";
$form.="<option value =\"snooker\">Snooker</option>";
$form.="<option value =\"golf\">Golf</option></select>";
$form.="<br><br><br><input type=\"submit\" value=\"Submit\"></form>";
echo($form);
}
else
{
require_once('../mysql_connect.php');
$sql = "select id from $leaderboard where id=\"$id\"";
$rs = mysql_query( $sql)
or die( "Could not execute query" );
#get number of rows that match
$num = mysql_numrows( $rs );
if( $num > 0 )
{ echo("You are already on that leaderboard.");
}
else{
echo("You will be added");
echo(" id =$id"); #DEBUG output=nothing
echo(" leaderboard = $leaderboard");#DEBUG output=tennis
echo(" num = $num"); #DEBUG output=nothing
}
}
?>


To make it clearer, the user I am using has an id of 2 and is on the tennis leaderboard. So basically when tennis is selected you would expect the message to be output "You are already on that leaderboard. The #DEBUG are in there to demonstrate what output I am getting. I know the $id is working until the IF statement because it outputs 2, same with the $leaderboard, that outputs "tennis".

I will give more detail if anyone requires. Like I said though I'm sure it is the way I am putting $id in the SQL statement!?

Thanks for any help.

Roger.

 

[Chacalinc]

add an echo $sql under the sql statment:

$sql = "select id from $leaderboard where id=\"$id\"";
echo $sql;

what do you get?

you could try:
$sql = "select * from " .$leaderboard. " where id='".$id."'";

Cheers

 

[DRJ478]

What column type is id? Probably INT not varchar.
I'd recommend to use a numerical value, so no quotes are necessary in the SQL.

Do what Chaclinc suggested and paste the SQL in you favorite SQL admin tool and observe the result.

 

[rogwilco]

Ok I put echo $sql in and it shows id at the end as "". So yes it looks like the mix up is because id is an int. I now understand this. What is the correct syntax then for the $id?

i.e.

$sql = "select * from " .$leaderboard. " where id='".$id."'"; (this doesn't work)

I have tried several variations but cannot seem to crack it? Help please, believe me I am trying!

 

[Chacalinc]

is there any posibility that require_once('../mysql_connect.php'); is affecting to $id? may be $id is redefined in mysql_connect.php?

 

[rogwilco]

No there is no mention of $id in mysql_connect.php.

 

[DRJ478]

Ok, here's what I think:
You assign $id from $_SESSION['id'].
However, there is no place in the script where the assignment of any value to $_SESSION['id'] happens.
Inspect $_SESSION with print_r($_SESSION) to find out what's actually in there.
ALl I can see is that there is no value set to $_SESSION in this script.

 

[rogwilco]

hmmm interesting... but still confused. I added two print($_SESSION). One before the form is submitted and that gave a result of :Array ( [first_name] => Jane [id] => 2 )

Obviously that is is what we want to see. However the other one gave a result of Array(). Below is my entire code for the page. I think the problem is I am not passing the SID after the submit button. How do you do that and also why would you need to do that as we are still in the same php script? Where would the SID info go???

Code:

<?php
session_name('YourVisitID');
ini_set('session.use_cookies', 0);
session_start();
#error_reporting(E_ALL);
$page_title = 'Add yourself onto a leaderboard!';
include ('./header.inc');
?>
<?php

$self = $_SERVER['PHP_SELF'];
$username = $_POST['username'];
$id = $_SESSION['id'];
$leaderboard = $_POST['leaderboard'];

?>

<?php
print_r($_SESSION); #GIVES:Array ( [first_name] => Jane [id] => 2 )
echo("$id"); #DEBUG result=2
if((!$leaderboard))
{
$form ="<p><font face=\"Arial, Helvetica, sans-serif\">Please select a leaderboard to add yourself onto.</font></p>";
$form .="<form action=\"$self\" method=\"post\">";
$form .="<select name=\"leaderboard\" size=\"1\" value=\"$leaderboard\">";
#$form.="<option value = NULL>Select a Board</option>";
$form.="<option value =\"squash\">Squash</option>";
$form.="<option value =\"badminton\">Badminton</option>";
$form.="<option value =\"tennis\">Tennis</option>";
$form.="<option value =\"snooker\">Snooker</option>";
$form.="<option value =\"golf\">Golf</option></select>";
$form.="<br><br><br><input type=\"submit\" value=\"Submit\"></form>";
echo($form);
}
else
{
require_once('../mysql_connect.php');
#create the query
$sql = "select * from $leaderboard where id=\"$id\"";
#$sql = "select * from $leaderboard where id=$id";
echo $sql;
print_r($_SESSION); #GIVES:Array()
#execute the query
$rs = mysql_query( $sql)
or die( "Could not execute query" );
echo mysql_error();
#get number of rows that match
$num = mysql_num_rows( $rs );
if( $num > 0 )
{ echo("You are already on that leaderboard.");
}
else{
echo("You will be added");
echo(" leaderboard selected = $leaderboard"); #DEBUG
echo(" id =$id"); #DEBUG
echo(" num = $id"); #DEBUG
}
}
?>



<?php
include ('./footer.inc'); // Include the HTML footer.
?>

 

[DRJ478]

There are several things I don't understand:
1. You set the session management to turn cookies off. But you have no other means of sending the sessionID along.
2. I still don't see anywhere an assignment of 'id' to the session variables. Where do the values come from? A part of the script we don't see?
Where did Jane come into the picture?

How does the script behave if you close all instances of your browser? Jane and the id could be leftovers of an old session.

We are still in the same PHP script...

Yes we are, but we are not in the same PASS. PHP is not like an 'application' that runs a script continuously. The first pass spits out the HTML for the form.
Conversation over, server 'hangs up'. Then the client 'calls back'. Here's where the SID comes in. It identifies the client and the server is able to go back an retrieve the session information of the previous pass. No SID, the server doesn't know who you are.
Clear?

 

[rogwilco]

On a previous page there is a login, thus $_SESSION['name'] and $_SESSION['id' are set. Also remember that the print_r(SESSION) and my echo("$id") before the form is put on screen all prove that the id has been passed across from the previous login screen.

The point where it either loses it or gets mixed up with it being an int is after I have clicked submit.

 

[DRJ478]

The SID is passed from your first script to the second - proven by the print_r($_SESSION). How? Does your previous script also set the session cookies to OFF?
DO you have transparent session ID enabled?

session.use_trans_sid boolean

session.use_trans_sid whether transparent sid support is enabled or not. Defaults to 0 (disabled).

URL based session management has additional security risks compared to cookie based session management. Users may send a URL that contains an active session ID to their friends by email or users may save a URL that contains a session ID to their bookmarks and access your site with the same session ID always, for example.

 

[rogwilco]

print_r($_SESSION) outputs Array ( [id] => 7* ).

*or whatever the id is of the person who has just logged on.

Surely that proves that id has been passed across. It even passes across the users name i.e.( [name] => someone).

All my scripts have cookies set to off. as I'm passing (trying) the SID with the lnik or header, etc.

It works until the submit button is pressed, then ALL $_SESSION data is lost.

"DO you have transparent session ID enabled?"

I will look into that. However I don't see how that can be an issue, if the other scripts worked!?

 

[rogwilco]

Just checked and session.use_trans_sid is off on the server.

 

[DRJ478]

Here's an important detail:

I'm passing (trying) the SID with the lnik or header, etc.

So, you are passing it 'manually'. Please show the code from the previous script that achieves that. We are makeing progress here.

 

[rogwilco]

Ok this is from the page prior which sends the SID:

<form name="form2" method="post" action="add_leaderboards.php?<?php echo SID;?>">
<input name="Input" type="submit" value="Get onto a Leaderboard"></form>

The script before that creates the session data and passes it to the above page as shown below:

if( ( !$username ) or ( !$password ) )
{ $msg = '<P>Please complete the fields</P>';
}

require_once('../mysql_connect.php'); //connect to the DB

$sql = "select id, first_name from users where username=\"$username\" and password =\"$password\" ";

$result = mysql_query( $sql)
or die( "Could not execute query" );

$row = mysql_fetch_array ($result, MYSQL_NUM);
if ($row) {
//Start the session, register the values and redirect.
$_SESSION['first_name'] = $row[1];
$_SESSION['id'] = $row[0];
header("Location:

http://www.mysite.com/loggedin.php?"

. SID);
exit();
}else{
$msg .= '<P>The username and password are incorrect</P>';
}
mysql_close();

Some of it not relevent but you get the idea.

BTW thankyou ever so much for the input I REALLY do appreciate it after 2 days of hair pulling...

 

[DRJ478]

Sorry, but you'll probably pull out more hair when you realize what the cause is.
Observe that you are adding the SID to the form's action attribute in the previous page. The action attribute in the form to add to the leaderboard lacks that. Simple.

 

[rogwilco]

But HOW do I add that to the submit button? I wouldn't of thought this would be the case as we are still in the same php page, i.e. my form action is $self which means data processing is in this page.

If though you are right, how do I add the SID on this line below?

$form .="<form action=\"$self\" method=\"post\">";

 

[DRJ478]

You have in the previous page:

<form name="form2" method="post" action="add_leaderboards.php?<?php echo SID;?>">

All you need is to append the SID to $self:

$self = $_SERVER['PHP_SELF']."?".SID;

That's it. Alternatively add it in the line

$form .= 'form action="'.$self.'?'.SID.'" method="post">';

 

[rogwilco]

Thanks ever so much DRJ478! It now works and recognizes that the user is already on that leaderboard. I know it took some time to actually get the full scenario out of me but thanks for being patient!

FULLY APPRIECIATED!