$sql not returning results

[cspgsl]

I need an update form on a page that will look to see if anything is entered in the users "welcome" field in their record and, if there is content, display it for editing / updating.

If there is nothing in the user's field enter "My Default Text" and use that to update the user's welcome field in their record.

Thus far I have the following.

In the login form
[code]<input type="submit" name="kt_login1" id="kt_login1" value="Login" />[/code]
In the page that opens as a result of loggin in:
[code]<? session_start();
        if (isset($_POST['kt_login1'])):
         $_SESSION['kt_login_user'] = $_POST['kt_login_user'];
         $_SESSION['kt_name'] = $_POST['kt_name'];
        endif;
?>
<p>Hello <? echo  $_SESSION['kt_name'] ?> Choose a link to update a part of your records...</p>
[/code]
In the page that is to contain the update record form:
[code]
<? session_start();
        if (!isset($_SESSION['kt_login_user'])):
        $_SESSION['kt_login_user'] = "Anonymous";
        $_SESSION['kt_name'] = $_POST['kt_name'];
        $_SESSION['kt_welcome'] = $_POST['kt_welcome'];
endif;
?>
<p>Hello <? echo  $_SESSION['kt_name'] ?> Yada yadda...</p>
<?php $sql = "SELECT 'welcome' FROM `users` WHERE `id` = $kt_login_user";
$qry = mysql_query($sql);
if(@mysql_num_rows($qry) > 0)
{
while($r = mysql_fetch_array($qry))
{
$kt_welcome = $r[1];
}
}
else
{
$kt_welcome = "My default text";
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $_SESSION['kt_welcome']; ?></textarea>
<?php mysql_close(); ?>
[/code]
I have created 2 users, one with content in the welcome field of their record and the other with a blank welcome field in the welcome field of their record.

What is happening at the moment is
1 - The "echo $_SESSION['kt_name" is returning the name of the logged in user (as expected)
2 - the only thing that is being returned in the textarea is My default text. The contents of the user with pre-existing data in his welcome field is not being returned in spite of being logged in.

I am coming to honestly believe that PHP was developed to drive me nuts.

I would appreciate some direction here. Many thanks in advance.

[ober]

Change:
$r[1];

To:
$r[0];

You're only selecting one field from the database and the fetch_array function starts the array at 0.

[cspgsl]

Thanks but that didn't make any difference.

[!--quoteo(post=371621:date=May 5 2006, 02:01 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 5 2006, 02:01 PM) [snapback]371621[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Change:
$r[1];

To:
$r[0];

You're only selecting one field from the database and the fetch_array function starts the array at 0.
[/quote]

[ober]

I would also suggest putting quotes around $kt_login_user in this statement:
SELECT 'welcome' FROM `users` WHERE `id` = '$kt_login_user'

Failing those changes, I'd suggest verifying that the query is executing correctly and you have something other than the Default text in the database.

[cspgsl]

In the database fields one user's record has "This the text in the database" and the other user's record is blank

[.josh]

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
would also suggest putting quotes around $kt_login_user in this statement:
SELECT 'welcome' FROM `users` WHERE `id` = '$kt_login_user'
[/quote]
so this didn't work either? you still need them so leave them there. try echoing $kt_login_user see if it's containing the value you want it to contain. maybe it's somehow not being passed right, so nothing is being selected from the db.

[cspgsl]

[!--quoteo(post=371650:date=May 5 2006, 03:04 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 5 2006, 03:04 PM) [snapback]371650[/snapback][/div][div class=\'quotemain\'][!--quotec--]
so this didn't work either? you still need them so leave them there. try echoing $kt_login_user see if it's containing the value you want it to contain. maybe it's somehow not being passed right, so nothing is being selected from the db.
[/quote]


Yes, it returns the username (this is a positive thing)

[.josh]

so the column in your database that you use to hold the user name is called 'id'? just making sure, because most people use 'id' for an id number not the user name.

in other words, are you sure it's not supposed to be:
SELECT 'welcome' FROM `users` WHERE `[!--coloro:red--][span style=\"color:red\"][!--/coloro--]username[!--colorc--][/span][!--/colorc--]` = '$kt_login_user'

or some other such column name?

[.josh]

also, if your 'id' column does indeed hold the user's name, then try changing this (assuming that you changed the 1 to a 0 earlier):

$kt_welcome = $r[0];

to this:

$kt_welcome = $r['welcome'];

also can you please update what your code looks like now, in a new post, just to make sure we're all on the same page.

[cspgsl]

[!--quoteo(post=371655:date=May 5 2006, 03:25 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 5 2006, 03:25 PM) [snapback]371655[/snapback][/div][div class=\'quotemain\'][!--quotec--]
so the column in your database that you use to hold the user name is called 'id'? just making sure, because most people use 'id' for an id number not the user name.

in other words, are you sure it's not supposed to be:
SELECT 'welcome' FROM `users` WHERE `[!--coloro:red--][span style=\"color:red\"][!--/coloro--]username[!--colorc--][/span][!--/colorc--]` = '$kt_login_user'

or some other such column name?
[/quote]


Doh...

however, I changed it to
[code]<?php $sql = "SELECT 'welcome' FROM 'users' WHERE 'username' = '$kt_login_user'"; [/code]
but the results are the same

here is the structure of the table
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Field Type Null Default
id int(11) No
username varchar(32) No
password varchar(32) No
name varchar(50) No
lastname varchar(50) No
email varchar(50) No
designation varchar(50) Yes NULL
firm varchar(50) No
address varchar(50) No
box varchar(50) Yes NULL
suite varchar(20) Yes NULL
city varchar(20) No
province varchar(20) No
postalcode varchar(20) No
phone varchar(20) No
fax varchar(20) Yes NULL
level varchar(10) No
active varchar(50) No user
random varchar(50) No
regtime datetime Yes 0000-00-00 00:00:00
welcome text No
[/quote]

The code re-posted

[code]<? session_start();
        if (!isset($_SESSION['kt_login_user'])):
        $_SESSION['kt_login_user'] = "Anonymous";
        $_SESSION['kt_name'] = $_POST['kt_name'];
        $_SESSION['kt_welcome'] = $_POST['kt_welcome'];
endif;
error_reporting(E_ALL);
?>
[/code]
[code]
<?php echo $kt_login_user ?>
  
<p> </p>
<?php $sql = "SELECT 'welcome' FROM 'users' WHERE 'username' = '$kt_login_user'";
$qry = mysql_query($sql);
if(@mysql_num_rows($qry) > 0)
{
// you have a result
while($r = mysql_fetch_array($qry))
{
$kt_welcome = $r['welcome'];
}
}
else
{
$kt_welcome = "My default text";
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $_SESSION['kt_welcome']; ?></textarea>
<?php mysql_close(); ?>
[/code]

[ober]

Ok... a few more things I noticed.

1) You're setting this: $kt_welcome;
You're echoing this: echo $_SESSION['kt_welcome'];

Those are not the same.

2) You don't need a while loop:
while($r = mysql_fetch_array($qry))
{
$kt_welcome = $r['welcome'];
}

becomes:
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

[.josh]

so let me get this straight:

if the user puts something in the textbox that stores it in the 'welcome' column, when you check to see if there is an entry in 'welcome', if it exists, it's supposed to make $kt_welcome = $r['welcome'] but if not then it simply makes $kt_welcome = 'My default text."

so as of right now, you are saying that your script does indeed print out "My default text" when it is supposed to, but it is blank when there is something in the 'welcome' column?

[cspgsl]

[!--quoteo(post=371667:date=May 5 2006, 03:46 PM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ May 5 2006, 03:46 PM) [snapback]371667[/snapback][/div][div class=\'quotemain\'][!--quotec--]
so let me get this straight:

if the user puts something in the textbox that stores it in the 'welcome' column, when you check to see if there is an entry in 'welcome', if it exists, it's supposed to make $kt_welcome = $r['welcome'] but if not then it simply makes $kt_welcome = 'My default text."
[/quote]

Boy, am I new...
I haven't got to creating the update form yet although I would think that to be the least of my worries. If I get the correct data in the text area when the query is run then I figure that taht is working (maybe I'm wrong here)

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]so as of right now, you are saying that your script does indeed print out "My default text" when it is supposed to, but it is blank when there is something in the 'welcome' column?[/quote]
No, right now it only prints My default text. It should print "Bill's data" when user #2 is logged in and My default text when #1 is logged in.



Could you please explain
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
1) You're setting this: $kt_welcome;
You're echoing this: echo $_SESSION['kt_welcome'];

Those are not the same.
[/quote]


Changed but result the same
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
<?php echo $kt_login_user ?>

<p>&nbsp;</p>
<?php $sql = "SELECT 'welcome' FROM 'users' WHERE 'username' = '$kt_login_user'";
$qry = mysql_query($sql);
if(@mysql_num_rows($qry) > 0)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];
}
else
{
$kt_welcome = "My default text";
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $_SESSION['kt_welcome']; ?></textarea>
<?php mysql_close(); ?>
[/quote]

[ober]

You're setting a normal variable and echoing a session variable. Replace it with the following.

[code]<?php echo $kt_login_user ?>

<p>&nbsp;</p>
<?php $sql = "SELECT 'welcome' FROM 'users' WHERE 'username' = '$kt_login_user'";
$qry = mysql_query($sql);
if(@mysql_num_rows($qry) > 0)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];
}
else
{
$kt_welcome = "My default text";
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>
<?php mysql_close(); ?> [/code]

[cspgsl]

[!--quoteo(post=371675:date=May 5 2006, 03:58 PM:name=ober)--][div class=\'quotetop\']QUOTE(ober @ May 5 2006, 03:58 PM) [snapback]371675[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You're setting a normal variable and echoing a session variable. Replace it with the following.
[/quote]

Same I'm afraid

[.josh]

okay then repost your updated code let's see what it looks like now.

[alpine]

unless i misunderstood this problem, i suggest:
[code]
$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user'");

if(mysql_num_rows($qry) == 1) // user found
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome OR $kt_welcome =="") // if empty message
{
$kt_welcome = "My default text";
}

}
[/code]

** EDITED a query cut'n paste error *lol*

[cspgsl]

Reposting code
[code]<? session_start();
        if (!isset($_SESSION['kt_login_user'])):
        $_SESSION['kt_login_user'] = "Anonymous";
        $_SESSION['kt_name'] = $_POST['kt_name'];
        $_SESSION['kt_welcome'] = $_POST['kt_welcome'];
endif;
error_reporting(E_ALL);
?>[/code]
[code]<?php echo $kt_login_user ?>

<p>&nbsp;</p>
<?php $sql = "SELECT 'welcome' FROM 'users' WHERE 'username' = '$kt_login_user'";
$qry = mysql_query($sql);
if(@mysql_num_rows($qry) > 0)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];
}
else
{
$kt_welcome = "My default text";
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>
<?php mysql_close(); ?>[/code]

[cspgsl]

[!--quoteo(post=371682:date=May 5 2006, 04:06 PM:name=alpine)--][div class=\'quotetop\']QUOTE(alpine @ May 5 2006, 04:06 PM) [snapback]371682[/snapback][/div][div class=\'quotemain\'][!--quotec--]
unless i misunderstood this problem, i suggest:
[code]
$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user'");
if(mysql_num_rows($qry) == 1)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome OR $kt_welcome =="")
{
$kt_welcome = "My default text";
}
}
[/code]

** EDITED a query cut'n paste error *lol*
[/quote]

I changed the code to reflect your edit
I received [!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/pencils/public_html/interview/home_page.php on line 47
[/quote]
so I added @ to [code]if(@mysql_num_rows($qry) == 1)[/code]

With the change to your suggested the problem is reversed. Now the textarea contains the correct data for the user with data in the field and no entry for the user with no data in the field (which I assume is returning what it sees (empty) in the field. It should return "My default text" if there is no data in the users field.

Code update:
[code]<?php echo $kt_login_user ?>

<p>&nbsp;</p>
<?php $qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user'");
if(@mysql_num_rows($qry) == 1)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome OR $kt_welcome =="")
{
$kt_welcome = "My default text";
}
}
?>
<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>
<?php mysql_close(); ?>
[/code]



Alpine:
I don't understand this line
[code]if(!$kt_welcome OR $kt_welcome =="")
[/code]

[cspgsl]

Hope I haven't upset anyone...

[alpine]

okay, something weird with your num_rows, and thats causing your problem, new one here:

[code]
<?php

echo $kt_login_user

print "<p>&nbsp;</p>";

$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user' limit 1");
if($qry)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome OR $kt_welcome =="")
{
$kt_welcome = "My default text";
}
}
?>

<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>

<?php
mysql_close();
?>
[/code]

!$kt_welcome OR $kt_welcome =="" checks if variable dont exist or is empty/doesnt contain any values (you can prefer to use the function empty() in some cases)

I assume you only have unique usernames in your db.... else this will fail for sure

[cspgsl]

Same problem, not returning "My default text" when field is empty. I tested it by putting something into the empty field and it returned the new value.

I very much appreciate everyone's time btw.

[!--quoteo(post=371700:date=May 5 2006, 04:54 PM:name=alpine)--][div class=\'quotetop\']QUOTE(alpine @ May 5 2006, 04:54 PM) [snapback]371700[/snapback][/div][div class=\'quotemain\'][!--quotec--]
okay, something weird with your num_rows, and thats causing your problem, new one here:

[code]
<?php

echo $kt_login_user

print "<p>&nbsp;</p>";

$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user' limit 1");
if($qry)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome OR $kt_welcome =="")
{
$kt_welcome = "My default text";
}
}
?>

<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>

<?php
mysql_close();
?>
[/code]

!$kt_welcome OR $kt_welcome =="" checks if variable dont exist or is empty/doesnt contain any values (you can prefer to use the function empty() in some cases)

I assume you only have unique usernames in your db.... else this will fail for sure
[/quote]


only two usernames and they are unique

[alpine]

*lol* so simple and yet so hard - but THIS TIME.....
[code]
<?php

$kt_welcome = "My default text";

echo $kt_login_user;

print "<p>&nbsp;</p>";

$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user' limit 1");
if($qry)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];
}
?>

<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>

<?php
mysql_close();
?>
[/code]

[cspgsl]

Sorry... back to the original scenario... now only returning "MY default text" for both users

[!--quoteo(post=371706:date=May 5 2006, 05:03 PM:name=alpine)--][div class=\'quotetop\']QUOTE(alpine @ May 5 2006, 05:03 PM) [snapback]371706[/snapback][/div][div class=\'quotemain\'][!--quotec--]
*lol* so simple and yet so hard - but THIS TIME.....
[code]
<?php

$kt_welcome = "My default text";

echo $kt_login_user;

print "<p>&nbsp;</p>";

$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user' limit 1");
if($qry)
{
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];
}
?>

<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>

<?php
mysql_close();
?>
[/code]
[/quote]

[alpine]

my latest had a bug, would not return default text if user was found - but...

stripped:
[code]

<?php

echo $kt_login_user;

print "<p>&nbsp;</p>";

$qry = mysql_query("SELECT welcome FROM users WHERE username = '$kt_login_user' limit 1");
$r = mysql_fetch_array($qry);
$kt_welcome = $r['welcome'];

if(!$kt_welcome)
{
$kt_welcome = "My default text";
}


?>

<textarea name="welcome" cols="80" rows="20"><?php echo $kt_welcome; ?></textarea>

<?php
mysql_close();
?>
[/code]

we could go on forever