single field query

[sgray]

hey there,

im making a webpage where i can simply type an addy in the form of http://webpage.com/index.php?name=holiday to bring up an article on a holiday I had. ive made a mysql db with two fields, name and content. can someone please tell me the code or (help me out) to let me present only a single entry in my db?. in the past ive made db's and queried the whole thing into an html table for display, im just not sure how to make queries on on certain parts of the db. Hope you understand what I mean. I hope to be able to type the above adress and be displayed with the right name and content entry.

Thanks a lot in advance for any help,

-Stew

[scoppc]

$SQL="SELECT name FROM table WHERE name='$name'";

By doing so, you have performed query to a single column.
Is that really what you mean?

[sgray]

right, so my code should look a little like this so far:

<?
$db = mysql_connect('localhost','user','password');
mysql_select_db("db", $db);
$SQL="SELECT name FROM frozenpea WHERE name='$name'";

where frozenpea is my db name and 'name' is the name of one of the fields. From there how do i go about dislaying the queried data?

[jmcneese]

http://www.php.net/manual/en/ref.mysql.php

[sgray]

cheers for the link, almost got it working as i would like it. My code is as follows:

<?php

$link = mysql_connect('localhost','user','pass') or die("Could not connect");
print "Connected successfully";
mysql_select_db("db") or die("Could not select database");

/* Performing SQL query */
$query = "SELECT name FROM frozenpea WHERE name='$name'"; 
$result = mysql_query($query) or die("Query failed");


/* Printing results in HTML */
print "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    print "\t<tr>\n";
    foreach ($line as $col_value) {
        print "\t\t<td>$col_value</td>\n";
    }
    print "\t</tr>\n";
}
print "</table>\n";

/* Free resultset */
mysql_free_result($result);

/* Closing connection */
mysql_close($link);

?>

everything works, when I type http:/url/index.php?name=$name

it returns the field 'name' from the sql db but not the contents of the 'content' field. can anyone suggest what needs adding so its lists both the name field and the content field.

thankyou

-stewart

[jmcneese]

$link = mysql_connect('localhost','user','pass') or die("Could not connect"); 
print "Connected successfully"; 
mysql_select_db("db") or die("Could not select database"); 

/* Performing SQL query */ 
$query = "SELECT name,content FROM frozenpea WHERE name='$name'"; 
$result = mysql_query($query) or die("Query failed"); 


/* Printing results in HTML */ 
print "<table>\n";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { 
    print "<tr><td>$row['name']</td><td>$row['content']</td></tr>\n"; 
} 
print "</table>\n"; 

/* Free resultset */ 
mysql_free_result($result); 

/* Closing connection */ 
mysql_close($link); 

[sgray]

ive been fiddling with that code for the last hour or so and are unable to get it working.

I get:

Parse error: parse error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in muck.php on line 15

lines 15 being the 'print "<tr><td>$row['name']</td><td>$row['content']</td></tr>\n";' line

[edwardsbc]

Change the line to this:

print "<tr><td>{$row['name']}</td><td>{$row['content']}</td></tr>\n";

PHP gets confused about whether or not the square brackets are for the variable or if they are supposed to be printed. The curly brackets are used to tell PHP explicitly that what follows is a variable (all of it).

[sgray]

thanks a lot guys, working sweet as.

Cheers!

[siwis]

You could also declare those array values to variables, then there's no issue with confusing php anyway.

old:

print "<tr><td>{$row['name']}</td><td>{$row['content']}</td></tr>\n";

new:

$name = $row['name'];
$content = $row['content'];
print "<tr><td>$name</td><td>$content</td></tr>\n";

One other possible suggestion, bearing in mind you are querying on a text string and not a numeric string, consider using LIKE

"SELECT row FROM table WHERE field LIKE $string"

That way, it should pick up on incorrect capitalisations.
Alternatively, add a field to the database with INT value auto incrementing, and the query on the integer value NOT the text value.

[sgray]

just though of an addition to the script, would it be possible to add a function so that if the url http://url.com/index.php?name=blah is typed but the field entry 'blah' does not exist, it would return some text saying 'no such content' or something to that extent?

[siwis]

Yep, just set up an if>else based upon the number of rows returned by MySQL:

$rows = mysql_num_rows($yourquery);
if ($rows != "1") {
echo "no such content";
} else {
## Do what you want with the data
};

[sgray]

siwis: I get:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in main.php on line 8
no such content.

maybe I used it wrong...

<?
$link = mysql_connect('localhost','user','pass') or die("Could not connect");
mysql_select_db("db") or die("Could not select database");

$query = "SELECT name,content FROM frozenpea WHERE name='$name'";
$result = mysql_query($query) or die("Query failed");

$rows = mysql_num_rows($name);
if ($rows != "1") {
echo "no such content";
} else {
print "<table>\n";

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "<tr><td>{$row['name']}</td><td>{$row['content']}</td</tr>\n";

}
print "</table>\n";
};

mysql_free_result($result);

mysql_close($link);
?>

[siwis]

Substitute:
$rows = mysql_num_rows($result);

Your asking php to tell you how many rows were returned by $name in your code. You need it to tell you how many rows were returned by the actual query, ie $result

HTH

[sgray]

Ahh I see, I understand it now 🙂 Thanks a lot for your help siwis, really appreciated