do data types need to be specified while polling a MySQL database?

[medaswho]

i am testing my php interaction with the database.  i am running a small script to retreive and display items stored in the database.  it doesn't show any result if i test on a row with an email address or if there are caps or white space in any field.  the only row that my code will retrieve ans display is on the one row in which i wrote "test" in caps in every collumn. can anyone tell me how to solve this problem?

 

here is the code...

$result = mysql_query("SELECT * FROM members

WHERE screenname = '".$_POST["sname"]."'") or die(mysql_error()); 

 

if($result) {

  while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

        $somedata  = $row['somedata'];

        $sname = $row['screenname'];

        echo $somedata."<br>";

        echo $sname;

            }

          }

[Mchl]

Nothing's wrong here on first look.

Could you post your table structure and data example?

[medaswho]

this is how my database was set up:

 

        screenname                  VARCHAR(20)  NOT NULL,

        somedata                    CHAR(32)    NOT NULL,

        date_created                DATE        NOT NULL,

        member_firstname            VARCHAR(30)  NOT NULL,

        member_lastname            VARCHAR(30)  NOT NULL,

        genre                      CHAR(20),

        is_group                      CHAR(3),

        stage_name                  VARCHAR(30),

        my_email                      VARCHAR(50)  NOT NULL,

 

where

[9three]

Sanitize the inputs of users.

 

mysql_real_escape_string($variable)

 

See if that helps

[medaswho]

that was a good thought 9three, but it didn't work for me.

[Mchl]

Can you also show some data from this table, and what do you put into $_POST["sname"] to perform search?

Are you sure that in all cases the query actually runs (i.e. no mysql error is displayed)?

[medaswho]

there is all kinds of data in there. it's all junk for test purposes.  some have spaces like the word "hip hop" for genre, some have words capitolized and sone or two are just lower case words all the way across, like "test".

[Mchl]

Ok then...

Try this

$query = "SELECT * FROM members WHERE screenname = {$_POST['sname']}";
echo $query;   // display the query to see if it is correct
$result = mysql_query($query) or die(mysql_error().": $query");  

[medaswho]

i am not sure how this was supposed to help, it is a slightly different query , wouldn't i have to test on my query?  but that not with standing...i did try it with your query and i got this:

 

SELECT * FROM members WHERE screenname = testUnknown column 'test' in 'where clause': SELECT * FROM members WHERE screenname = test

 

does that tell you anything?

[Mchl]

Oh yeah... I screwed up. Should be:

$query = "SELECT * FROM members WHERE screenname = '{$_POST['sname']}'";

 

That's same query as yours, only using different PHP syntax.

[medaswho]

ok that returns no error.  also when i go to phpMysqlAdmin i can see all the data in there.  i have discovered that everything i insert with the phpMyAdmin comes back fine, but things i have inserted thru my own php forms i have not been able to retrieve.  i am using mysql_real_escape_string() on the query variables that insert new data.

[Mchl]

So you should also use it, for retrieving data.

 

$sname = mysql_real_escape_string($_POST['sname'])
$query = "SELECT * FROM members WHERE screenname = '$sname'";

[medaswho]

this is so freakin madening!!! what you typed makes it look as if i need to use that function on my query, but i also have to use it on the variables that will be displayed, right?

 

  while($row = mysql_fetch_array($result, MYSQL_BOTH)) {

        $pass_check  = $row['pass_word'];

        $sname_check = $row['screenname'];

              $pass_check  = mysql_real_escape_string($pass_check);

              $sname_check = mysql_real_escape_string($sname_check);

        echo $pass_check."<br>";

        echo $sname_check."<br>";

            }

[Mchl]

No. You only need to use it on variables that go into database. Whether they're actual data to be stored, or as part of WHERE condition in SELECT query.

If used properly you will not need any additional processing for displaying.