Page 1 of 2
syntax problem, full code provided
Posted: Sun Dec 14, 2003 12:35 pm
by malcolmboston
ok i need some help on defining a mysql_query
heres the background, i have a session var created at login page (username) when the person is authenticiated its says hello 'yourname'
ok cool, thats what i wanted, now im going a little more in-depth
i want to be able to tell the user all of there information stored in my mysql database (login_DB)
heres the code:
Code: Select all
<?php
// database variables for connections
$host = "localhost";
$user = "malcolmboston";
$password = "xxxxx";
$DBname = "TDN";
$tablename = "login_DB";
//connection variables completeed
// establishing connections
$link = mysql_connect ($host, $user, $password);
//connection established
//the query defined
$query = "select * from $tablename";
//the result
$result =mysql_db_query($DBname, $query, $link);
echo $result;
?>
ok thats no good because it will return everything (with no discretion about the actual user logged in), the above code works according to zend ive never used it in a page
here what i want but the syntax is wrong and i cant find anything about it, anyone know the proper coding?
Code: Select all
$query = "SELECT password from $tablename WHERE username = $_SESSION['username']";
any ideas on the correct syntax?
Posted: Sun Dec 14, 2003 12:39 pm
by MrNonchalant
Try:
Code: Select all
$query = "SELECT `password` FROM `$tablename` WHERE `username`='$_SESSIONї'username']'";
Posted: Sun Dec 14, 2003 12:41 pm
by malcolmboston
id love to mate but im not at my computer at the moment
i had it working yesterday (using DW) but now it wont work, and id rather code it myself DW adds alot of <span style='color:blue' title='I'm naughty, are you naughty?'>smurf</span> (and old syntax) to your code
anyone else think any differently to my problem
Posted: Sun Dec 14, 2003 12:44 pm
by MrNonchalant
My example above should work unless it's a PHP code problem instead of a MySQL syntax problem. Which, given the error you encountered, isn't probable.
Posted: Sun Dec 14, 2003 12:46 pm
by malcolmboston
well it is definitely a problem with this piece of coding
everything works fine when i make a simple query without the session in it, just fails when i add that bit of syntax
Posted: Sun Dec 14, 2003 12:48 pm
by infolock
or this :
Code: Select all
$query = "SELECT password from ".$tablename." WHERE username = '".$_SESSION['username']."'"
Posted: Sun Dec 14, 2003 12:49 pm
by malcolmboston
ive wondered this for a while,
why the . before certain syntax that dont require it at other times?
Posted: Sun Dec 14, 2003 12:55 pm
by infolock
well, the . just adds it to whatever string you are trying to declare.
it's just a method of exiting the string so you can declare the variables or arrays or whatever else it is you want without the hassle of using exit characters ( such as \ ... ).
so, this string declaratino :
$sql = "Select password from ".$tablename." ....."
just exits the string, adds the variable to the string, and then reopens the string to add the rest of the parameters.
Posted: Sun Dec 14, 2003 12:56 pm
by malcolmboston
ahh that must be my problem, i think youve hit the nail on the head there
ill bear that in mind for future programming, i think its a very important snippet you provided
Thanks
Posted: Mon Dec 15, 2003 2:56 am
by malcolmboston
ok thanks, that worked a treat (syntax wise anyway)
for some godforesaken reason it constantly returns a value of resource ID, both in zend and dreamweaver and the outputted PHP page.
There is no value of resource ID #2 in my database, which it keeps on telling me the value of the query is, has anyone had this before!?
How do i fix it, im gettin worried, deadline is coming up and this site needs to be 'shipped' any explanation would be fantastic
heres the code as always
i am 99% certain this is correct, if theres any typos its because i cant remember if there was any errors in it that i fixed
Code: Select all
<?php
// database variables for connections
$host = "localhost";
$user = "malcolmboston";
$password = "xxxxxx";
$DBname = "TDN";
$tablename = "login_DB";
//connection variables completeed
// establishing connections
$link = mysql_connect ($host, $user, $password);
//connection established
//the query defined
$query = "SELECT password from ".$tablename." WHERE username = '".$_SESSION['username']."'"
//the result
$result =mysql_db_query($DBname, $query, $link);
echo $result;
Any explanation/help would be fantastic
edit : just thought id make the point that all the settings for connecting work and the query is exactly what i want it to do, all the fields that are being called are present and information is in them.
Posted: Mon Dec 15, 2003 3:06 am
by malcolmboston
ok, i remembered that google is my best friend
Resource ID #2 is the connection or result set. You need to use the
connection to do a query, which returns a result set, then use the result
set to do fetches. Those will return an array of the stuff you really
wanted.
so how exactly do i do this?[/quote]
Posted: Mon Dec 15, 2003 3:07 am
by twigletmac
Ok, first thing is to not use [php_man]mysql_db_query[/php_man]() - it's deprecated should be replaced by [php_man]mysql_select_db[/php_man]() and [php_man]mysql_query[/php_man](). Secondly $result is a result resource, not an array of data or anything like that and you can't echo it directly. Try:
Code: Select all
// database variables for connections
$host = "localhost";
$user = "malcolmboston";
$password = "xxxxxx";
$DBname = "TDN";
$tablename = "login_DB";
//connection variables completeed
// establishing connections
$link = mysql_connect ($host, $user, $password);
//connection established
//the query defined
$query = "SELECT password from ".$tablename." WHERE username = '".$_SESSION['username']."'";
// select the database
mysql_select_db($DBname);
// query the database
$result = mysql_query($query);
// test to see if anything is returned
if (mysql_num_rows($result) == 1) {
echo 'User exists.';
} else {
echo 'User does not exist.';
}
If you want to collect the value of the password have a look at [php_man]mysql_fetch_assoc[/php_man]() or [php_man]mysql_result[/php_man]().
The reason why:
Code: Select all
$query = "SELECT password from $tablename WHERE username = $_SESSION['username']";
did not work is because you cannot have single quoted element names within a double quoted string, you could have done the following instead:
Code: Select all
$query = "SELECT password from $tablename WHERE username = $_SESSION[username]";
or
Code: Select all
$query = "SELECT password from $tablename WHERE username = {$_SESSION['username']}";
Although the concenation method:
Code: Select all
$query = "SELECT password from ".$tablename." WHERE username = '".$_SESSION['username']."'"
is generally easier to read if you're using a syntax highlighter.
Mac
Posted: Mon Dec 15, 2003 3:12 am
by malcolmboston
ok it will be easier if i explain what i want this code to do
session is created with there username "posted" from the login_form
the query will allow people to see there username
another query there password
another query there email
and so on and so forth
really very very simple stuff, nothing to complex about it at all
but seeing as im new to arrays (and have never before used them) how exactly do i retrieve the data from the query i just performed?
sorry for being a burden

Posted: Mon Dec 15, 2003 3:25 am
by malcolmboston
c'mon just a snippet!
wheres your christmas spirit!

Posted: Mon Dec 15, 2003 3:39 am
by JayBird
adding to Mac's code, enter this
Code: Select all
$line = mysql_fetch_array($query, MYSQL_ASSOC);
echo $line['username'];
Mac gave you some links to read about this, check them out.
Mark