PHP data connection 3

[LWolf]

Still the noob...
Why does this code bomb out?

$data = mysql_query("SELECT * FROM users WHERE username=" . $_REQUEST["username"])

or

$data = mysql_query("SELECT * FROM users WHERE username= $_REQUEST["username"])

I having been playing with this all afternoon and not getting anywhere.

Thanks.
K

 

[merlinx]

in the first one you have [dot] dollar request.

in the both you have an opening " with out the closing portion and the same problem with the username turning off the string and opening a new string with out being closed.

try,

$who = $_REQUEST["username"];
$data = mysql_query("SELECT * FROM users WHERE username= $who");

or build your entire string first,

$who = $_REQUEST["username"];
$sql = 'SELECT * FROM users WHERE username='.'\' '.$who.'\' ';
$data = mysql_query($sql);

 

[merlinx]

correction;

[dot] dollar request , I looked over that to fast.

in the both you have an opening " with out the closing one, pertains only to the second one.

but look at $sql

if $_REQUEST["username"] for example is alfred, it is turned into 'alfred'

 

[LWolf]

That worked, thanks. Quick question...now that I have a recordset in $data what would I use to see the data? I have tried

...
echo $data("usrGroup")

and

echo $data["userGroup"]

where userGroup is a field in the table.

 

[merlinx]

your request will return either false or an array.

first off,
if($data !== false)

1)
$variable = mysql_fetch_array($data);

2)
while($result = mysql_fetch_array($data ))
{
$resultSet[] = $result;
}


3) it's 2:15 in the morning here in Germany, I'll have eye on this post for about another 1/2 hour, after which I'll see anything tomorrow. (forum time + 6 hours. if your still having difficulties try google with,

php mysql select
php mysql mysql_fetch_array
and such. helps quite well

 

[merlinx]

also echo array's with

echo '<pre>';
print_r ($variable ); // from first example
print_r ($resultSet); // from second example
echo '</pre>';

 

[LWolf]

i have also tried....

$data = mysql_query($sql);
$qRow = mysql_fetch_array($data);
echo "/" . $qRow["usrGroup"] ."/"

i tried...

echo (!$data) and got a 1.

 

[merlinx]

echo (!$data) and got a 1.
following script,

<?php
echo (!false);
?>

outputs 1 // not false = true

so, starting from ground up,

$host ="host"; // Servername
$user ="who"; // MySQL-Benutzer
$pwd ="pass"; // Password
$db ="db"; // Database
$table = "myTable" // select from this table

$con = mysql_connect($host, $user, $pwd);
if($con === false) // no connection to mysql

$db = mysql_select_db($db);
if($db === false) // no connection to databse

$res = mysql_query($sql);
if($res === false) // failure in query

otherwise

while($res = mysql_fetch_array($res ))
{
$MyData[] = $res ;
}
// if count $MyData equals 0, no matchs found.

if ($res !== false){ mysql_free_result($res); }
if ($con === false){ $ret = mysql_close( $con ); }

 

[merlinx]

another correction,

if ($con === false){ $ret = mysql_close( $con ); } // wrong
if ($con !== false){ $ret = mysql_close( $con ); } // better

 

[LWolf]

ok, there are 2 records in the table 1 user named 'keith'. According to the display I should be able to access and display the record whose username is 'keith'. So why doesn't the code display the username?

here is what I got...

CODE....
$host ="localhost";
$user ="keith";
$pwd ="mypassword";
$db ="mydatabase";
$table = "Users";

$con = mysql_connect($host, $user, $pwd);
if($con == false) echo "no connection to mysql";

$db = mysql_select_db($db);
if($db == false) echo "no db";

$who = $_REQUEST['username'];
$sql = 'SELECT * FROM Users WHERE username='.'\' '.$who.'\' ';
$res = mysql_query($sql);
if($res == false) echo 'bad query';
$data = mysql_query($sql);

if($data == false){
echo 'No Records';
}
else {
echo '/' . $data['username'] . '/';
echo 'Records';
}

RESULTS...
//Records

 

[feherke]

Hi

Let me try again :
[ul]
[li][tt]mysql_query()[/tt] returns
[ul]
[li]for query statements
[ul]
[li]on success a resource[/li]
[li]on failure [tt]false[/tt][/li]
[/ul][/li]
[li]for data manipulation statements
[ul]
[li]on success [tt]true[/tt][/li]
[li]on failure [tt]false[/tt][/li]
[/ul][/li]
[/ul][/li]
[/ul]
[tt]mysql_fetch_array()[/tt] ( and similar mysql_fetch_*() functions ) extract one row of data from a resource and returns it in an array ( or whatever structure the function name indicates.

[navy]$host[/navy] [teal]=[/teal] [green][i]"localhost"[/i][/green][teal];[/teal]
[navy]$user[/navy] [teal]=[/teal] [green][i]"keith"[/i][/green][teal];[/teal]
[navy]$pwd[/navy] [teal]=[/teal] [green][i]"mypassword"[/i][/green][teal];[/teal]
[navy]$db[/navy] [teal]=[/teal] [green][i]"mydatabase"[/i][/green][teal];[/teal]

[navy]$con[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_connect[/color][teal]([/teal][navy]$host[/navy][teal],[/teal] [navy]$user[/navy][teal],[/teal] [navy]$pwd[/navy][teal]);[/teal]
[b]if[/b] [teal]([/teal][navy]$con[/navy] [teal]==[/teal] [b]false[/b][teal])[/teal] [b]die[/b][teal]([/teal][green][i]"no connection to mysql"[/i][/green][teal]);[/teal]

[navy]$db[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_select_db[/color][teal]([/teal][navy]$db[/navy][teal]);[/teal]
[b]if[/b] [teal]([/teal][navy]$db[/navy] [teal]==[/teal] [b]false[/b][teal])[/teal] [b]die[/b][teal]([/teal][green][i]"no db"[/i][/green][teal]);[/teal]

[navy]$who[/navy] [teal]=[/teal] [navy]$_REQUEST[/navy][teal][[/teal][green][i]'username'[/i][/green][teal]];[/teal]
[navy]$sql[/navy] [teal]=[/teal] [green][i]'SELECT * FROM Users WHERE username='[/i][/green] [teal].[/teal] [COLOR=darkgoldenrod]mysql_real_escape_string[/color][teal]([/teal][navy]$who[/navy][teal]);[/teal]
[navy]$res[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_query[/color][teal]([/teal][navy]$sql[/navy][teal]);[/teal]
[b]if[/b] [teal]([/teal][navy]$res[/navy] [teal]==[/teal] [b]false[/b][teal])[/teal] [b]die[/b][teal]([/teal][green][i]'bad query'[/i][/green][teal]);[/teal]
[navy]$data[/navy] [teal]=[/teal] [COLOR=darkgoldenrod]mysql_fetch_array[/color][teal]([/teal][navy]$res[/navy][teal]);[/teal]

[b]if[/b] [teal]([/teal][navy]$data[/navy] [teal]==[/teal] [b]false[/b][teal])[/teal] [teal]{[/teal]
    [b]echo[/b] [green][i]'No Records'[/i][/green][teal];[/teal]
[teal]}[/teal] [b]else[/b] [teal]{[/teal]
    [b]echo[/b] [navy]$data[/navy][teal][[/teal][green][i]'username'[/i][/green][teal]];[/teal]
    [b]echo[/b] [green][i]'Records'[/i][/green][teal];[/teal]
[teal]}[/teal]

[ul]
[li]Never pass unescaped values to the SQL queries. Use [tt]mysql_real_escape_string()[/tt] or prepared statements with parameters to protect your database from SQL injection.[/li]
[li]Is pointless to check the success of the connection or any other function if then you still let the script continue its normal flow despite the error.[/li]
[/ul]


Feherke.

http://feherke.github.com/

 

[jpadie]

remember also to enquote strings in mysql queries.

$sql = "SELECT * FROM Users WHERE username='[red]"[/red] . mysql_real_escape_string($who) . [red]"'"[/red];

 

[feherke]

Hi

Oops. Thanks. ( Got used with other DB modules where surrounding quotes are added on escaping. )


Feherke.

http://feherke.github.com/

 

[merlinx]

my 2 in the morning syntax...

If you copied and pasted the code directly, then your user name was preceded with an empty space on the left side,

$sql = 'SELECT * FROM Users WHERE username='.'\'[highlight] [/highlight]'.$who.'\'[highlight] [/highlight]';

my 2 in the afternoon syntax...

$sql = [/color blue]'SELECT * FROM Users WHERE username='[/color green].[/color blue]'[/color green]\'[/color red]'[/color green].$who.[/color blue]'[/color green]\'[/color red]'[/color green];[/color blue]

 

[jpadie]

@merlinx, that looks a bit complex. but not incorrect.

using your method this would be one step easier

$sql = 'SELECT * FROM Users WHERE username=\''.$who.'\''; //avoid the first break out

but even then, the escaping and breakouts are not really necessary.

the "normal" way of enquoting sql strings is to use double quotes around the query and single quotes around the variables. this just makes life easier. alternatives are heredoc, (v)sprintf etc. But there is nothing wrong with using single quotes, escapes and breakouts. just ends up with less readable code.

$who = mysql_real_escape_string($_REQUEST['username']);
$query = "SELECT * FROM Users WHERE username='$who'";
//or
$query = <<<SQL
SELECT *
FROM   Users
WHERE username = '$who'
SQL;
//or
$query = sprintf("SELECT * FROM Users WHERE username='%s'", mysql_real_escape_string($_REQUEST['username']));
//all the above will end up the same

 

[merlinx]

one star for jpadie from me.
;-)

I'm still in my first year of using php, and I think I've got good grasp on things. none the less I'm still in my write more, do less phase.

 

[LWolf]

merlinx!

fantastic help, especially at 2am! I had been at it for 10 hours and not really accomplished anything. your help has been invaluable!

JPadie

Love the help!

I am a 12 year vet of ASP and literally just sat down 2 days ago to work on php. Obviously a learning curve and I appreciate everyone's help. Today I have not been able to look at the issue. I believe in the tighter the code the better. I will look at the tomorrow.

Thank you both!!!!!

Keith

 

[jpadie]

i've been coding in php for a while now. and more generally in various languages since 1978.

I have just started with objective C and am horrified by how over-engineered it is for simple tasks.

I am also playing with arduino and its language which is fairly straightforward but leads to really interesting programming conundra when thinking about how to code dynamic action timers (and just about anything) whilst maintaining state in a single-threaded environment. You basically have to imagine the entire operating system's functionality in a single loop, and code for that. Looking forward to Raspberry Pi availability solving this issue for me ...

basically, i think i've been spoiled by how easy, forgiving and powerful php is, and what benefits are brought by multi-threading!

 

[LWolf]

Ok, here is the code and below that the results. The result should still display the passed username. I believe that the code is connecting to the database and the table. Do I have the code wrong for displaying the value in the recordset?

Code...

$host ="localhost";
$user ="mysql_username";
$pwd ="mysql_password";
$db ="mysql_databse";
$table = "Users";
$con = mysql_connect($host, $user, $pwd);
if($con == false) echo "no connection to mysql";

$db = mysql_select_db($db);
if($db == false) echo "no db";

$who = $_REQUEST['username'];
$sql = 'SELECT * FROM Users WHERE username=\''.$who.'\'';
$res = mysql_query($sql);
if($res == false) echo 'bad query';

$data = mysql_query($sql);
echo "'" . $sql . "'<br>";

if($data == false){
echo 'No Records';
}
else {
echo '/' . $data['username'] . '/';
echo 'Records';
}


Results...

'SELECT * FROM Users WHERE username='keith''
//Records

 

[merlinx]

are the extra quotes part of the string?
'[/color red]SELECT * FROM Users WHERE username='keith'[/color green]'[/color red]

try it without the variable,

$sql = '[/color blue]SELECT * FROM Users WHERE username=\'keith\'[/color green]';[/color blue]

or without breakouts,

$sql = "[/color blue]SELECT * FROM Users WHERE username='keith'[/color green]";[/color blue]

second,

$data = mysql_query($sql); you are quering a second time.

you need to use,
$res = mysql_query($sql);
$data = mysql_fetch_array($res);