[SOLVED] Passing variable to SQL query and getting full database

[WebGuyRI]

Newbee to mysql and php looking to extract data from a database which I setup on line. Have tried a lot of examples from tutorials and searching the web. Have been able to get it all working but one item which I am really struggling with..

 

I am trying to extract select data from a mysql database. Using two pages one with the form and the other with the database code. Tried both post and get from form on the form page and $_POST and $_GET on the second page.

 

The data passes from the form page to the code page OK, as I am echoing it on the code page. The issue is the script runs on page the code and shows the full database (only six items in database for testing).but not just the one item I am searching for. Would someone please help as I know its must be a newbee item I am missing, thanks.

 

---------------------------------------------

<?php

// posting

echo $_POST['name'];

 

// Connect to database

$hostname = "";

$username = "";

$password = "";

$dbname = "";

$usertable = "test1";

$yourfield = "name";

$yourfield2 = "Given";

$yourfield3 = "Name 2";

$yourfield4 = "Vol and Page";

 

mysql_connect($hostname, $username, $password) or DIE("Unable to connect to MySQL server $hostname");

print "  Connected to MySQL server<br>";

$selected = mysql_select_db($dbname) or DIE("Could not select requested db $dbname");

 

print "Connected to database $dbname<br>";

 

$query = "SELECT * FROM $usertable";

$result = mysql_query($query) or DIE("Could not Execute Query on table $usertable");

if ($result) {

 

    print "Query successful on table $usertable<br><br>";

    print "Your Query returned:<br>";

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

 

        print "".$row{$yourfield}." ".$row{$yourfield2}." ".$row{$yourfield3}." ".$row{$yourfield4}."<br>";

        }

 

    }

 

mysql_close;

?>

----------------------------------

This is what it returns ("LAJ" was the text I put in the search box on page one)

-------

LAJ Connected to MySQL server

Connected to database afgs_test

Query successful on table test1

 

Your Query returned:

SHEA Ida M. BOURGEOIS 134-1

LAJ Denise H 134-2

REMILLARD Annette L

LA Annette L. REMILLARD 134-3

PARENT Lionel R. 13

Bill

[wildteen88]

Your using the incorrect syntax here:

print "".$row{$yourfield}." ".$row{$yourfield2}." ".$row{$yourfield3}." ".$row{$yourfield4}."<br>";

 

it should be

print $row['yourfield']." ".$row['yourfield2']." ".$row['yourfield3']." ".$row['yourfield4']."<br>";

 

You should edit your post and remove the hostname/username/password for mysql from your code.

 

 

[WebGuyRI]

Modified as suggested and now the database shows this but not the info on LAJ

LAJ Connected to MySQL server

Connected to database

Query successful on table

 

Your Query returned:

[ldougherty]

He missed the fact that your items were variables.

 

Change this

 

print $row['yourfield']." ".$row['yourfield2']." ".$row['yourfield3']." ".$row['yourfield4']."<br>";

 

to this

 

print $row['$yourfield']." ".$row['$yourfield2']." ".$row['$yourfield3']." ".$row['$yourfield4']."<br>";

 

or if you want to remove the variable references change it to this

 

print $row['name']." ".$row['Given']." ".$row['Name 2']." ".$row['Vol and Page']."<br>";

 

Either of those will return the full results from the database.

 

Now if I understand your original question you don't want to return the full results, you only want to return the results where name is LAJ right?

 

If so change your query to this.

 

$query = "SELECT * FROM $usertable WHERE name = '$_POST['name']'";

 

Hope that helps.

[WebGuyRI]

Hi Larry,

 

Thanks for the feedback. You are correct in you comment as I am trying to only get out of the database only the names that match my search string. Made the changes you mentioned one at a time and the first one displayed as before and when I added this

$query = "SELECT * FROM $usertable WHERE name = '$_POST['name']'";

 

Received a blank screen, went back to the original query and the page displayed again but still not with the name I am trying to show.

 

PS How do I edit my original message?

 

Thanks

 

[wildteen88]

You should use then following when placing any data from the user

$name = mysql_real_escape_string($_POST['name']);

$query = "SELECT * FROM $usertable WHERE name = '$name'";

 

PS How do I edit my original message?

There is a time limit for editing posts. I think it is something like 10mins or so. However a moderator has edited your post for you and removed your database credentials.

[WebGuyRI]

Your are all great thanks for all the help, it works.

 

Take Care

 

Bill