Trying to Use First and Last Names to Call a Row

outpost

Hi Everyone ...

I've been stealing code and ideas from you ... and everyone else for the past month. This script trys to build an HTML page (bottom) from a row array referenced by passed data on a previous page link.

I simply don't know how to use the $first and $last names to accurately call a single row.

==============================================

<?php

//---------------------------------------------- CONVERT PASSED DATA

$query=getenv(QUERY_STRING);
parse_str($query);

// Parse data received from faculty directory

$names = explode("&", $query);

// Separate data into the two names

$last = $names[0];
$first = $names[1];

// Create variable titles for each name

//---------------------------------------------- CONNECT

$conn = mysql_connect("localhost", "directory", "****");

//---------------------------------------------- ERROR MESSAGES

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
};


if (!mysql_select_db("outpost")) {
    echo "Unable to select mydb: " . mysql_error();
    exit;
};

//---------------------------------------------- QUERY DATABASE

$sql = "SELECT * FROM faculty WHERE '$last'=`last_name` ";

$result = mysql_query($sql);

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

    extract($row);

};

//---------------------------------------------- ERROR MESSAGES

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
};


if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
};

//---------------------------------------------- FREE MEMORY

mysql_free_result($result);

//---------------------------------------------- HTML PAGE

<HTML>

<HEAD><TITLE>Directory</TITLE></HEAD>

<BODY>

<TABLE>

</BODY>

</HTML>

==============================================

The problem I'm encountering is that it works with just a "last_name" unless there are two last names that are identical. I can't seem to add a "first_name" as an additional qualifier (WHERE '$last'=last_name AND '$first'=first_name), maybe because there are many first names that are the same. The script seems to choke on rows with identical names ("No rows found, nothing to print so am exiting").

But maybe there's another way ...?

I'm running:

    Apache/1.3.27 (Win32) 
    PHP Version 4.3.1
    MySQL 3.23.55-nt 
    WinXP

Any suggestions will be appreciated. 🙂

sijis

i think its just your sql query that's the problem.

$sql = "SELECT * FROM faculty WHERE '$last'=last_name ";

should be

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = ''$first';

simple sql syntax:
SELECT field1, field2 FROM table WHERE field1 = 'value';
// alternatively
SELECT * FROM table WHERE field1 = 'value';

also if you are getting values from a querystring in the URL use this instead:
file.php?first=Tommy&last=Boy

$REQUEST['first']; // value is Tommy
$REQUEST['last']; // value is Boy

if its from a form.
depends on what method your using in <form> tag.
$POST['first'] and $POST['last']
$GET['first'] and $GET['last']

so you can get rid of the
---- CONVERT PASSED DATA --- area.

hope that helps.

outpost

Thanks for the help sijis ...

The $REQUEST['first']; and $REQUEST['last']; lines work perfectly! ... and I'm going to steal them from you! 🙂

When I tried the:

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = ''$first';

I got this error message:

Parse error: parse error, unexpected T_STRING in (php file) on line 38

... which is now from the error message line:

echo "Could not successfully run query ($sql) from DB: " . mysql_error();

When I removed all the query error message code, I got the same

Parse error: parse error, unexpected T_STRING in (php file) on line 66

... which was this HTML line

which seems to perfectly work well when I only run the:

$sql = "SELECT * FROM faculty WHERE '$last'=`last_name` ";

line (... without an identical last name of course).

Thanks again for the suggestion and for the very tidy $_REQUEST[] method!

=========================== NEWEST CODE

<?php

//---------------------------------------------- CONNECT

$conn = mysql_connect("localhost", "directory", "****");

//---------------------------------------------- ERROR MESSAGES

if (!$conn) {
    echo "Unable to connect to DB: " . mysql_error();
    exit;
};


if (!mysql_select_db("outpost")) {
    echo "Unable to select mydb: " . mysql_error();
    exit;
};

//---------------------------------------------- QUERY DATABASE

$_REQUEST['first']; 
$_REQUEST['last']; 

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = ''$first';

$result = mysql_query($sql);

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

    extract($row);

};

//---------------------------------------------- ERROR MESSAGES

if (!$result) {
    echo "Could not successfully run query ($sql) from DB: " . mysql_error();
    exit;
};


if (mysql_num_rows($result) == 0) {
    echo "No rows found, nothing to print so am exiting";
    exit;
};

//---------------------------------------------- FREE MEMORY

mysql_free_result($result);

//---------------------------------------------- HTML PAGE

<HTML>

<HEAD><TITLE>Directory</TITLE></HEAD>

<BODY>

<TABLE>

</BODY>

</HTML>

Are you left handed? 🙂

sijis

ok, here's another tip
usually when you see a "parse error" of some sort you are missing one of several things, a semicolon, a single or double quote and curly brackets.

in this case its the single quote for the query:
$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = ''$first';

it should have read:

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = '$first'";
(i put the double quote in front of the $first var instead of after the last single quote.
it should work fine now.

also usually when you solve the first problem the other ones go away! 🙂

outpost

Thanks for trying sijis ...

I played around with various single and double quotes and tilde quotes (depending on what I read over the past week or so).

I DID change the $sql line to:

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = '$first'";

... but now I'm getting my ol' ... "No rows found, nothing to print so am exiting" error message.

Just in case the db is incorrectly configured, my table structure looks like:

CREATE TABLE directory (
first_name varchar(35) NOT NULL default '',
last_name varchar(35) NOT NULL default '',
division varchar(75) NOT NULL default '',
discipline varchar(50) NOT NULL default '',
location varchar(35) NOT NULL default '',
building_room varchar(35) NOT NULL default '',
email varchar(35) NOT NULL default '',
PRIMARY KEY (last_name),
KEY last_name (last_name),
KEY first_name (first_name),
KEY division (division),
KEY discipline (discipline),
KEY location (location),
KEY building_room (building_room),
KEY email (email),
) TYPE=MyISAM COMMENT='Basic directory';

I use phMyAdmin 2.3.3pl1 and am trying to learn how to configure the db.

Thanks again for your suggestions. 🙂

sijis

ok.
could it be that you have not defined $last and $first?

change your sql to this:
$sql = "SELECT * FROM faculty WHERE last_name = '".$POST['last']."' AND first_name = '".$POST['first']."'";

you could have alternatively done:
$last = $POST['last'];
$first = $POST['first'];

$sql = "SELECT * FROM faculty WHERE last_name = '$last' AND first_name = '$first'";

if that still give you 0 rows, then try this:
(using example just above)
$sql = "SELECT * FROM faculty WHERE last_name = '$last' OR first_name = '$first'";

if still nothing... check the spelling on the value you are inputting.
also check that the name value in the <input type=text name="first"> HTML tag, matches the $_POST['first'] like so.

outpost

sijis ... ! ! ! ! ! ! ! ! EUREKA ! ! ! ! ! ! ! !

I went through all your options in various combinations and this was the one that worked!

$_REQUEST['first']; 
$_REQUEST['last'];

$sql = "SELECT * FROM faculty WHERE last_name = '$last' OR first_name = '$first'";

It seems like the "AND" would be a basic way to deal with multiple data fields. Weird ....

Thanks much for all your efforts in helping us PHP newbies. We appreciate it! 🙂

sijis

great!

note:
$REQUEST['first'];
$REQUEST['last'];

is the same as:
$first
$last
if you have registered_globals set to 'on'. (found in php.ini or phpinfo(); )

basically you can remove
$REQUEST['first'];
$REQUEST['last'];

and the query will still work.

glad to help 🙂