Newbie Horror

NathanH · 02-19-2008, 10:33 PM

Okay, i'm guessing this is either not going to be possible or i'm just very stupid and have overlooked it. Probably the latter, but i'm learning :P

I've been trying to (it's going to sound easy) get a value out of the database and into a table cell. I can do that, but I need to take a member specific value out. As in, I can take out a value, but i can only show all values or only one, and it would be the same for everyone.

Maybe i'm not clear enough - i'm trying! :)

I'll give you the scenario:
- I have one table: 'users',
- I have 4 fields: 'ID', 'username', 'password', 'gold',
- I am trying to paste the member's gold on a page.

My code:

Code:

$showgold = "SELECT * FROM users";
$query = mysql_query($showgold);
while($row = mysql_fetch_array($query))
{
echo "<table>";
echo "<tr>";
echo "<td>" . $row['gold'] . "</td>";
echo "</tr>";
echo "</table>";
}

This shows me every member's amount of gold. What i'm trying to have is just the member seeing their amount of gold. I've been looking for an answer more or less all day. Maybe you guys/girls could help? :)

Thanks

Wildhoney · 02-19-2008, 10:47 PM

First and foremost, welcome to the community, Nathan! I hope you stick around and mingle!

As for your little problem, you will be wanting to add a WHERE clause to your SQL query. The WHERE clause is synonymous with -- I want only these rows! The way you would do that is, as you have an ID in your table, every member has a unique member ID -- or at least should have if that row is the primary key, as well as unique and, optionally, auto_increment.

Let's assume that the user's ID is stored in a session variable called id. You could output the ID in your PHP code like so:

php Code:

var_dump($_SESSION['id']);

Now, to add the WHERE clause, and only pull back the one record for the unique member ID, you would construct your query to look like the following, considering my member ID is 5:

sql Code:

SELECT
    gold
FROM
    users
WHERE
    id = 5

Thus, if the ID is in $_SESSION['id'] then I have everything I possibly need to build that query and execute it:

php Code:

$szSQL = sprintf("SELECT gold FROM users WHERE id = %d", $_SESSION['id']);

If the sprintf function is somewhat alien to you, and I understand it might be -- we've all come from the ground upwards! Then may I recommend a quick read of not only the documentation page on the wonderful sprintf, but also an article right here at TalkPHP:

Securing your MySQL Queries with Sprintf

I hope this helps you! Incidentally, is this for a game? Do we good folks get a sneak preview?

maZtah · 02-19-2008, 10:49 PM

This should do the trick:

PHP Code:


			
$szQuery = 'SELECT gold FROM users WHERE id='.$iMemberId.' LIMIT 1;

$pResult = mysql_query($szQuery);



$aRow = mysql_fetch_array($pResult);

HTML Code:

<table>
<tr>
    <td><?php echo $aRow['gold']; ?></td>
</tr>
</table>

Where $iMemberId is the id of the user that is logged in.

PS. There's no need to do a while loop, since you're only fetching one row.

NathanH · 02-19-2008, 10:57 PM

Quote:

Originally Posted by Wildhoney

As for your little problem, you will be wanting to add a WHERE clause to your SQL query. The WHERE clause is synonymous with -- I want only these rows! The way you would do that is, as you have an ID in your table, every member has a unique member ID -- or at least should have if that row is the primary key, as well as unique and, optionally, auto_increment.

I hope this helps you! Incidentally, is this for a game? Do we good folks get a sneak preview?

I had tried the WHERE clause, but was missing the SESSION variable. I remember thinking about whether to use SESSIONs or not, but as I'm trying to get to grips with basic stuff I made the mistake of missing it out! :P

I will be going to bed now, but will definately read up on sprintf when I'm next back. Thank you for the help, the tips and the heads-up! :)

I told myself I would learn on the structure of a game, as it provides me with (what I hope) is mainly what I will be using with php & mysql. General register/login/logout, general account stuff and nothing too complicated (I say complicated after this embarrasment :P).

And as for a sneak preview- there really is nothing, my design is somewhat limited and because I'm new(ish) there's no functionality :)

NathanH · 02-19-2008, 11:00 PM

Quote:

Originally Posted by maZtah

This should do the trick:

PHP Code:


			
$szQuery = 'SELECT gold FROM users WHERE id='.$iMemberId.' LIMIT 1;

$pResult = mysql_query($szQuery);



$aRow = mysql_fetch_array($pResult);

HTML Code:

<table>
<tr>
    <td><?php echo $aRow['gold']; ?></td>
</tr>
</table>

Where $iMemberId is the id of the user that is logged in.

PS. There's no need to do a while loop, since you're only fetching one row.

Thank you very much maZtah :) I'll get onto that right when I wake up :)