php-windows Digest 18 Dec 2010 00:36:05 -0000 Issue 3898
Topics (messages 30451 through 30456):
Re: PHP Search DB Table
30451 by: David Robley
30452 by: Sascha Meyer
30453 by: Oliver Kennedy
30454 by: Oliver Kennedy
30455 by: Sascha Meyer
Need help getting syntax correct on 2 related problems.
30456 by: Bill Mudry
Administrivia:
To subscribe to the digest, e-mail:
php-windows-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-windows-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-wind...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
Oliver Kennedy wrote:
>
>
> Hello Everyone
>
> Apologies if I have not done this in the correct way, this is the first
> time I have turned to your for help so I am unaware if I have to submit
> this according to a certain protocol, if that is the case then please let
> me know and I will go through the proper channels. My problem is this.
>
> I have a very simple database consisting of 1 table, I want users here to
> be able to use a search function to query the database in order to return
> the information on a client by a specific ID number if it is in the
> database. My search form is this
>
> <body>
> <div id="container" >
> <form action='datasearch.php' method='POST'>
> <div id ="options">
> <p><label>Search by Client ID<br /><input type="text" name="searchx"
> /></label></p> <p><input type="submit" value="Search" /></p>
> </div>
> </div>
> </form>
> </body>
> </html>
>
> and my process form is this
>
> <?php
> $term = $_REQUEST['searchx'];
> mysql_connect("localhost", "root", "*********") or die (mysql_error());
> mysql_select_db("moneyl") or die(mysql_error());
> $result = mysql_query("SELECT * FROM clients WHERE clientid = '$term'")
> or die(mysql_error());
> echo"<table border='1'>";
> echo "<tr><th>Client ID</th> <th>Feeearner</th> <th>First Name</th>
> <th>Middle Name</th>
> <th>Last Name</th>
> <th>House/Flat Number</th>
> <th>Address</th>
> <th>Postcode</th>
> <th>Gender</th>
> <th>Date of Birth</th>
> <th>Landline</th>";
> while($row = mysql_fetch_array( $result ))
> {
> echo "<tr><td>";
> echo$row['clientid'];
> echo"</td><td>";
> echo$row['feeearner'];
> echo"</td><td>";
> echo$row['firstname'];
> echo"</td><td>";
> echo$row['middlename'];
> echo"</td><td>";
> echo$row['lastname'];
> echo"</td><td>";
> echo$row['hfnumber'];
> echo"</td><td>";
> echo$row['address'];
> echo"</td><td>";
> echo$row['postcode'];
> echo"</td><td>";
> echo$row['gender'];
> echo"</td><td>";
> echo$row['dob'];
> echo"</td><td>";
> echo$row['landline'];
> echo"</td></tr>";
> }
> echo"</table>";
> ?>
>
> I know the form is connecting an retrieving okay because if I change the
> WHERE client id = '' to a specific number of a client ID in the database
> then it comes back with that information, so essentially it is not pulling
> through the numbers entered into the search box the value for which I have
> used REQUEST for and specified it with the value $term.
>
> I am a beginner at this and am trying to do something to impress the
> powers-that-be at work, this is now the last stumbling block. Any help
> would be appreciated.
>
> Regards
>
> Oliver
Try
$query = "SELECT * FROM clients WHERE clientid = '$term'";
echo $query . '<br />';
$result = mysql_query($query);
And see what is actually in the query.
Cheers
--
David Robley
Reality is nothing but a collective hunch.
Today is Pungenday, the 56th day of The Aftermath in the YOLD 3176.
--- End Message ---
--- Begin Message ---
David wrote:
>
> Oliver Kennedy wrote:
> > ...
> > I have a very simple database consisting of 1 table, I want users here
> to
> > be able to use a search function to query the database in order to
> return
> > the information on a client by a specific ID number if it is in the
> > database.
> > ...
>
> Try
>
> $query = "SELECT * FROM clients WHERE clientid = '$term'";
> echo $query . '<br />';
> $result = mysql_query($query);
>
> And see what is actually in the query.
>
> Cheers
> --
> David Robley
As David stated, output the query and copy it into the MySQL console, see if
that gives you an error. You are talking about using an ID -number-, in that
case using single quotes around your $term variable is incorrect but won't
cause an error on MySQL servers.
Especially because you are new to PHP, I would recommend to do some reading
about making PHP secure, your code above has lots of possibilies for code
injection and SQL injection:
* Escaping variables for use in MySQL queries [1]
* SQL Injection [2]
* PHP and security [3]
Best regards,
Sascha
[1] http://de.php.net/mysql_real_escape_string
[2] http://de.php.net/manual/en/security.database.sql-injection.php
[3] http://de.php.net/manual/en/security.php
--
Freundliche Grüße / Kind regards,
Sascha Meyer
--------------------------------------------------
EE: http://www.experts-exchange.com/M_761556.html
ZCE: http://www.zend.com/en/yellow-pages#show-ClientCandidateID=ZEND011290
GRATIS! Movie-FLAT mit über 300 Videos.
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome
--- End Message ---
--- Begin Message ---
David, Sascha thank you both for your help. Using the query
$query = "SELECT * FROM clients WHERE clientid = '$term'";
echo $query . '<br />';
$result = mysql_query($query);
as suggested printed out the below
SELECT * FROM clients WHERE clientid = ''
This seems to indicate that it is not seeing the value within the single
quotes. However a slight caveats to that
If I change what is contained within the quotes to a specific number then it
does read it, for example
SELECT * FROM clients WHERE clientid = '123456';
So it is only when I enclose a value that it doesn't compute, I tried putting
the $term within %% like so
$query = "SELECT * FROM clients WHERE clientid = '%".$term"%'";
however this just returns we page not found, possibly because I need to use the
LIKE parameter in there somewhere?
Many thanks for all your help on this
Kind Regards
Oliver
--- End Message ---
--- Begin Message ---
Okay, thank you all again for your input, I have tried a number of suggestions
to work out what is happening. Just to reiterate here is the search form which
is on a different page.
<form action='datasearch.php' method='POST'>
<div id ="options">
<p><label>Search by Client ID<br /><input type="text" name="searchx"
/></label></p>
<p><input type="submit" value="Search" /></p>
</div>
shown below is the datasearch.php page
<?php
$term = $_REQUEST['searchx'];
mysql_connect("localhost", "root", "christmas") or die (mysql_error());
mysql_select_db("moneyl") or die(mysql_error());
$query = "SELECT * FROM clients WHERE clientid = '%".$term."%'";
echo $query . '<br />';
echo $term . '<br />';
$result = mysql_query($query);
echo"<table border='1'>";
echo "<tr><th>Client ID</th> <th>Feeearner</th> <th>First Name</th>
<th>Middle Name</th>
<th>Last Name</th>
<th>House/Flat Number</th>
<th>Address</th>
<th>Postcode</th>
<th>Gender</th>
<th>Date of Birth</th>
<th>Landline</th>";
while($row = mysql_fetch_array( $result ))
{
echo "<tr><td>";
echo$row['clientid'];
echo"</td><td>";
echo$row['feeearner'];
echo"</td><td>";
echo$row['firstname'];
echo"</td><td>";
echo$row['middlename'];
echo"</td><td>";
echo$row['lastname'];
echo"</td><td>";
echo$row['hfnumber'];
echo"</td><td>";
echo$row['address'];
echo"</td><td>";
echo$row['postcode'];
echo"</td><td>";
echo$row['gender'];
echo"</td><td>";
echo$row['dob'];
echo"</td><td>";
echo$row['landline'];
echo"</td></tr>";
}
echo"</table>";
?>
The printout of the query reveals this
SELECT * FROM clients WHERE clientid = '%%'
the echo of the value $term just showed a blank line.
Essentially the numbers entered into the search box which I am retrieving using
$term = $_REQUEST['searchx'];
are not being pulled through, am I using the correct funtion? I tried GET and
POST just to see what would happen an no joy.
So why does the above just display a blank value even when there are numbers in
the search box? I am very much a novice at this so it is not impossible that I
am making a very very basic mistake here but from the tutorials I have seen
this should work.
Many thanks for your help, this is the first time I have used this method to
obtain help and I have been very impressed with both the speed and quality of
the repsonse.
Kind Regards
Oliver
> From: aristotlek...@hotmail.co.uk
> To: harlequ...@gmx.de; php-wind...@lists.php.net; php...@lists.php.net;
> robl...@aapt.net.au
> Date: Tue, 14 Dec 2010 12:55:31 +0000
> Subject: RE: [PHP-WIN] Re: PHP Search DB Table
>
>
> David, Sascha thank you both for your help. Using the query
>
> $query = "SELECT * FROM clients WHERE clientid = '$term'";
> echo $query . '<br />';
> $result = mysql_query($query);
>
> as suggested printed out the below
>
> SELECT * FROM clients WHERE clientid = ''
>
> This seems to indicate that it is not seeing the value within the single
> quotes. However a slight caveats to that
> If I change what is contained within the quotes to a specific number then it
> does read it, for example
>
> SELECT * FROM clients WHERE clientid = '123456';
>
> So it is only when I enclose a value that it doesn't compute, I tried putting
> the $term within %% like so
>
> $query = "SELECT * FROM clients WHERE clientid = '%".$term"%'";
>
> however this just returns we page not found, possibly because I need to use
> the LIKE parameter in there somewhere?
>
> Many thanks for all your help on this
>
> Kind Regards
>
> Oliver
>
>
>
>
--- End Message ---
--- Begin Message ---
Hi Oliver,
Oliver wrote:
>
> Okay, thank you all again for your input, I have tried a number of
> suggestions to work out what is happening. Just to reiterate here is the
> search
> form which is on a different page.
> ...
Try the following code on your search page to display GET- and POST-variables
submitted by your form:
[CODE]
<?php
print "<pre>";
print "POST:<br />";
print_r($_POST);
print "<hr />";
print "GET:<br />";
print_r($_GET);
print "</pre>";
[/CODE]
I couldn't locate an error in your previously posted code so let's give this
one a try.
Regards,
Sascha
--
Freundliche Grüße / Kind regards,
Sascha Meyer
--------------------------------------------------
EE: http://www.experts-exchange.com/M_761556.html
ZCE: http://www.zend.com/en/yellow-pages#show-ClientCandidateID=ZEND011290
GRATIS! Movie-FLAT mit über 300 Videos.
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome
--- End Message ---
--- Begin Message ---
I have worked up an SQL script that needs expansion into two
different scripts to take care of two different needs. They can be
worked from one table, the 'species' file while the second will
probably need the species file to have a join to the sci_genus file.
First, perhaps I should put a few words in for those that have never
been exposed to botanical species and genera. All living things are
given botanical names by scientific convention. Each unique living
thing (from bacteria to fish to animals, plants and trees) are assigned
a unique two part name called the species name. Wood comes from
trees (of course) so the wood is also referred often by the tree's
scientific name.
The first word in a species name happens to be the name of the next
and higher organizational level above it, the genus. If you have a species
name, you are therefore able to extract the genus name. (genus is singular,
genera is plural, species is oddly singular and plural all the time
by the way). Here are some examples:
Species Name Genus name
Abies Alba Abies
Acacia farnesiana Acacia
Caesalpinia ebano Caesalpinia
Dalbergia oliveri Dalbergia
Santalum album Santalum
Problem 1
My pet project, a wood knowledge base is reporting over 15,000
botanical names of woods from
around the world. The database is called 'taxa'. information on all
wood species is kept in a table
called 'species'. Within the 18 fields in that one file, two relevant
fields included are 'species_name'
(which records all scientific species names and 'genus_name' which
holds all genus names.
Note again that from the same file (no JOIN needed), every genus name
can be read inside each
species name (as above). Unfortunately, the genera (plural of genus)
column is missing 1/4 or
more of the names it should have for every record of a species (
genera to species is a one to
many relationship). To extract all the needed data to insert to the
blank cells, I was able to put
together the following script (using phpadmin) that lists nicely on
the screen all the genera from
extracting them from all the species names:
SELECT LEFT(`species_name`, LOCATE(' ', `species_name`)-1)
FROM species_backup
WHERE `genus_name` IS NULL
note --- I used a copy of species I names species_backup to protect
the original file while
experimenting to see what will work out.
So far so good. Now all I should have to do is have the proper syntax
for a INSERT INTO
statement to write all the missing genera names and repair a fair
size chunk of errors (missing
data). Here is what I tried;
INSERT INTO species_backup ('genus_name')
SELECT LEFT(`species_name`, LOCATE(' ', `species_name`)-1)
FROM species_backup
WHERE `genus_name` IS NULL
I got the following error: #1062 - Duplicate entry '' for key 2
What did I do wrong? I must be close. What is the proper syntax that will work?
Once this runs ok, the missing data problem will be sold. I may,
though, work it
later into a short PHP script pointed to by a menu link for
occasional maintenance
of the website and its data.
Problem 2
Go to http://www.prowebcanada.com/taxa/ and choose Woody Orders from
the menu on the left.
It will show all the Orders that I have found have woody elements in
them. Pick one and
you will travel down the botanical tree to show the data sheet on the
chosen order.
Below that on the page will be all the woody Families that belong
under the chosen Order.
Choose one and .... we continue to work our way down the botanical
tree further until
a chosen Genus data page lists all woody Species under a Genus, we
pick one and end up
at the bottom of the tree and the data page for the chosen Species.
In other words, downward travel in the tree works quite well. After
some prior attempts,
I want to also get the upward path of child to parent relationships
finally working and
finished). There are three paths needed to complete the entire upward
travel means:
Child to Parent
Species to Genus
Genus to Family
Family to Order
The last two I am quite sure will need a JOIN statement between the
relevant child table
and the parent table while, as shown above, the genus name can be
extracted directly out
of the species name. That should be easier. Lets leave the last two
for some other day.
If you are on a species data page, the parameter species_name will be
in memory since
it had to be chosen at some stage to get there. A typical sample of
the link from a species listing to the one chosen species can be seen
by resting a mouse on one choice in the
listing of species, (using a GET statement) such as:
http://www.prowebcanada.com/taxa/displayspecies.php?species_name=Acacia
albida
The above first script works well to list ALL genera, but all I need
is the parent genus
for the child (for example above, the parent Genus for Acacia
albida). So what kind of statement can best do that? Once this works,
I can place it in a link statement so it is
possible to move not only down the tree but for the first time UP the
botanical tree one
level to a parent genus. (.... leaving only two more paths to develop
for complete upward
mobility).
I have an uncomfortable feeling that this should be a rather
elementary part of learning
PHP and MySQL. Please bear with me if it is. I have tried
combinations earlier this year
only to be faced with errors I could not handle.
You may wish to separate these two tasks into separate responses for ease of
discussion and handling.
These are two problems left of a short list of tasks in the project
before I can lay coding aside for a few months to concentrate on a
massive, huge amount of data input. It would
be nice if I got them all done as soon as possible to start the year
with data input instead.
Your help(s) always much appreciated,
Season's greetings and Merry Christmas,
Bill Mudry
Mississauga, Ontario Canada
--- End Message ---
