[RESOLVED] Output based on the value of one field

ddrfreek

This is the code I wrote to output the data stored in my MySQL database, determined by the session variable username. I don't get any kind of error, but the echo doesn't display anything, which makes me assume that it's not reading the data properly. Is there anything that I've clearly done wrong here? Could it be a problem with the loop? Thanks in advance for your help.

session_start(); 
include("database.php");
include("login.php");
$query="SELECT * FROM user WHERE username=$_SESSION[username]";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {

$title=mysql_result($result,$i,"title");
$name=mysql_result($result,$i,"phone");
$extension=mysql_result($result,$i,"extension");
$email=mysql_result($result,$i,"email");

echo "$title";

$i++;
}

stolzyboy

session_start(); 
include("database.php"); 
include("login.php"); 
$query="SELECT * FROM user WHERE username=$_SESSION[username]"; 
$result=mysql_query($query); 
$num=mysql_numrows($result); 

while ($row = mysql_fetch_array($result)) { 
//all $row[''blah'] are assuming your fields are named that
$title=$row['title']; 
$name=$row['phone'];  

$extension=$row['extension'];  

$email=$row['email']; 

echo $title;  

}

ddrfreek

Hmm, for some reason the loop isn't working. Could it be a problem with where I placed the code that connects to my database (which is before the html tag at the top of the document)?

Edit: Also, what's the difference between using printf, sprintf, or vprintf? Should I even be using any of them with this situation?

bradgrafelman

Array indeces in your case should be strings. $row[name] does not have a string as an index, but a constant. PHP will detect this (assuming there actually isn't a constant named 'name') and throw an E_WARNING and then assume it is a string. So, use quotes: $row['name'].
Arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. If they do, they should be wrapped in braces ( {} ). Instead, I usually escape out of the string and concatenate the array in the middle, like so:
```
echo 'Your name is: ' . $row['name'] . '! Hello there.';
```
If a MySQL query isn't working right, what is MySQL trying to tell you? Try debugging your query, e.g.:
```
mysql_query($query) or die('MySQL error: ' . mysql_error() . '<br/>Query: ' . $query);
```

EDIT: Also, if you wanted simple variable such as $title instead of$row['title'] then you can [man]extract/man the variables from the $row array on each iteration of the loop.

ddrfreek

Ok, thanks a lot for the help (although I'm still trying to figure out exactly what you're saying). In all cases of my script, there will only be one value for each field, because the row I'm selecting them from (username) is unique.

Also, I'm going to be outputting these values into a form (using the value parameter). So, should something like this

<input type=\"text\" name=\"mail\" maxlength=\"45\" value=\"$email\">

take the proper value for email?

ddrfreek

Ok, so I put that debug in there, and it told me this:
MySQL error: Unknown column 'doghousecomics' in 'where clause'
Query: SELECT * FROM users WHERE username=doghousecomics

But, doghousecomics isn't the column, it's just one of the values in the username field. I'm looking at the page with phpMyAdmin right now.
Would having the username field as the primary key be a possible problem? I can't think of anything else.

bradgrafelman

D'oh, that was the first problem I noticed.. forgot to mention it.

doghousecomics is a string you're trying to use in the WHERE statement. Just like in PHP, strings must be delimited in SQL with quotes. More specifically, single quotes.

ddrfreek

Oh my god...

XD after all that, all I needed were quotes. Thanks a lot man, I'm pretty sure it works now. I'm just going to mess around with it a bit before marking this as resolved.

bradgrafelman

Feel free to repost your code for critiquing.

ddrfreek

<?
$query="SELECT * FROM users WHERE username='$_SESSION[username]'";
$result= mysql_query($query) or die('MySQL error: ' . mysql_error() . '<br/>Query: ' . $query);

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$title=$row['title'];
$name=$row['name'];
$extension=$row['extension'];
$email=$row['email'];
}

if(!$logged_in){
   echo "<h1>Error!</h1>\n";
   echo "You are not currently logged in, you do not have access to this page. <a 

href=\"http://www.doghousecomics.com\">Back to DHC</a>";
}
else{
echo "<form action=\"<>\" method=\"post\">
<table align=\"left\" cellspacing=\"0\" cellpadding=\"3\">
<tr><td>Comic Title:</td><td><input type=\"text\" name=\"title\" maxlength=\"30\" value=\"$title\"></td></tr>
<tr><td>Your Name:</td><td><input type=\"text\" name=\"extension\" maxlength=\"3\" 

value=\"$name\"></td></tr>
<tr><td>Comic Extension:</td><td><input type=\"text\" name=\"extension\" maxlength=\"30\"  

value=\"$extension\"></td></tr>
<tr><td>E-mail:</td><td><input type=\"text\" name=\"mail\" maxlength=\"45\" value=\"$email\"></td></tr>
<tr><td colspan=\"2\" align=\"right\"><input type=\"submit\" name=\"save\" value=\"Save Changes\"></td></tr>
</table>
</form>";

}

?>

That's the whole thing, and so far it's working perfectly. Now I just need to make a file to process the form and upload the information back to the database (hence the form action being blank). Thanks for the help!

stolzyboy

it's always the silly things that get a guy... error catching is the best way to handle troubles, as brad says, i tend to, when helping others, assume their queries are correct, by them pasting it into cmd line or phpmyadmin or the like... so i rarely look there...

what i wanted to do was get you away from mysql_result method since when using large tables/results... it is far slower than mysql_fetch_array and the like...

glad you got it working!

stolzyboy

bradgrafelman

Just some comments to clean up the code further:

A "SELECT *" SQL query almost always uses more resources than you intend to use. It is always wiser - if nothing else but for scalability reasons - to name which columns you're going to use, e.g. "SELECT title, name, extension, email" .
As I mentioned earlier, arrays, more specifically items in arrays referenced by indeces, should not simply appear inside a double-quoted string. As such, the proper way to insert the $SESSION data in your SQL query could possibly be:
```
$query="SELECT title, name, extension, email FROM users WHERE username='" . $_SESSION['username'] . "'";
```
Are you only attempting to retrieve 1 row? If so, there are two things you should do: 1.) add 'LIMIT 1' onto the end of your query, and 2.) get rid of the while() loop leaving $row = mysql_fetch_array($result, MYSQL_ASSOC).

ddrfreek

Alright, I made those changes, no problem at all. Thanks for all your effort (: