PHP form to query DB for ANY entry

zeroge · #1 (**permalink**) 01-01-10, 12:42

Hello

I have a two part script that allows me to search my DB for ANY ENTRY. I got the connection and everything going, except, it will ALWAYS return a "NO RECORDS FOUND" ...

I worked my eyeballs out and have almost a skinhead now from scratching my head ... maybe someone can help out?

This is the script which will display the results...

Quote:

<table border="1" cellpadding="5" cellspacing="0" bordercolor="#000000">
<tr>
<td width="120"><b>First Name</b></td>
<td width="120"><b>Last Name</b></td>
<td width="70"><b>Nickname</b></td>
<td width="100"><b>DOB</b></td>
</tr>
<tr>
<td>
<?php
$hostname = "host"; // DBserver.
$username = "User"; // DBusername
$password = "My PW"; // DBpassword
$usertable = "my table"; // TBLname
$dbName = "my DB"; // DBname

$conn = mysql_connect("host","User","My PW") or die ('Error connecting to mysql');
$dbname = 'my DB';
mysql_select_db($dbname);
?>
<?php
//error message (not found message)begins
$XX = "No Record Found, to search again please close this window";
//query details table begins
$query = mysql_query("SELECT * FROM clients WHERE $metode LIKE '%$search%' LIMIT 0, 50");
while ($row = @mysql_fetch_array($query))
{
$variable1=$row["first_name"];
$variable2=$row["last_name"];
$variable3=$row["nickname"];
$variable4=$row["dob"];
//table layout for results

print ("<tr>");
print ("<td>$variable1</td>");
print ("<td>$variable2</td>");
print ("<td>$variable3</td>");
print ("<td>$variable4</td>");
print ("</tr>");
}
//below this is the function for no record!!
if (!$variable1)
{
print ("$XX");
}
//end
?>
</table>

And this is the script for the actual form (another page)

Quote:

<form method="post" action="results.php" target="_blank">
<div align="center">
<table border="0" cellpadding="0" cellspacing="0">
<tr>
<td bordercolor="#000000">
<p align="center">
<select name="metode" size="1">
<option value="first_name">First Name</option>
<option value="last_name">Last Name</option>
<option value="nickname">Nickname</option>
<option value="dob">DOB</option>
</select>
<input type="text" name="search" size="25"> <br>
Search database: <input type="submit" value="Go!!" name="Go"></p>
</td>
</tr>
</table>
</div>
</form>

This would be a pretty cool script for people like me who want to add entries to a DB manually but want to check the DB if this entry already exists before crating a double entry! If we could get it going ...

Thanks a 8m times

r937 · #2 (**permalink**) 01-01-10, 13:00

i have two suggestions for you

first, if a database query doesn't seem to be working, debug it outside of your application language

i.e. run your query right in mysql, not via php

second, if you want to prevent duplicates, declare a UNIQUE constraint in the database and the database will do the checking for you

i.e. you don't have to search first before adding data

zeroge · #3 (**permalink**) 01-01-10, 13:16

TKS r937

I added a Unique constraint, but this would only work with unique data, i.e. email address, but it seems not to work with me.

I have another form I am using to populate the table with new entries. this is done manually from our PC in the office. It works great but it also seem to accept duplicate entries, that, despite of unique a key.

I have to add, this form we use for populating the DB manually, contains about 28 filed entries (it includes; first/last name, DOB, addresses, phone numbers and many other ... client data and most of this data can't have a unique key since "PETER" may be in the DB a 1000 times.)

Thanks

r937 · #4 (**permalink**) 01-01-10, 13:24

Quote:

Originally Posted by zeroge

It works great but it also seem to accept duplicate entries, that, despite of unique a key.

nobody can help you if you don't show details

please do a SHOW CREATE TABLE

also, how did your test of the query outside of php go?

healdem · #5 (**permalink**) 01-01-10, 13:33

having proved your SQL is working outside PHP then I'd alwasy reccomend that you assign the SQL to a variabel and then run the variable

eg

PHP Code:


			
//query details table begins
$strSQL= "SELECT * FROM clients WHERE $metode LIKE '%$search%' LIMIT 0, 50";
$query = mysql_query($strSQL);
while ($row = @mysql_fetch_array($query))

that way round you can examine the sql easily and ensure that you are sending valid SQL to the SQL engine.

zeroge · #6 (**permalink**) 01-01-10, 13:40

Quote:

nobody can help you if you don't show details

??? It's all there! ??? Except, I listed the result page code first...

I have the full code attached to my original post. OK, BACK TO FRONT ... where it says

Quote:

And this is the script for the actual form

This is a simple php page with a form (including a drop-down to choose the field I am looking in) and a text field to input the actual query. Let's say I look for "Peter" in first name, I would choose the field name "first_name" from the drop down list and and the type "Peter" into the text filed and press "submit" button

The "RESULT" page (results.php) would then open in another window, showing me a table and if there were "Peter" in the DB, it would list them in that table...

The complete script for tjhis result page is shown where it says;

Quote:

This is the script which will display the results

So I am really confused why you say "nobody can help if I show nothing"

r937 · #7 (**permalink**) 01-01-10, 13:44

Quote:

Originally Posted by zeroge

??? It's all there! ???

um... no it's not

also, this is the mysql forum, so all your php code might as well be chinese

Quote:

Originally Posted by zeroge

I added a Unique constraint, but this would only work with unique data

i wanted to see the results of the SHOW CREATE TABLE

just run this query --

Code:

SHOW CREATE TABLE clients

i wanted to see which UNIQUE keys you've declared

zeroge · #8 (**permalink**) 01-01-10, 13:50

Hello Healdem

Thanks for your reply..

Quote:

having proved your SQL is working outside PHP

I am not a MySQL wizard, rather new. I have run simple SQL in phpMyadmin and also used simple PHP to apply single SQL queries, even with "Where" statements and it always worked.

But this is quite a bit complex. I a also st up quite a large for to populate (insert data into the table) the DB and it also works. So while I know basics, I am sure still missing bit and pieces ... ;-)

Thanks for you guys' help and still a HAPPY NEW YEAR 2010 to everyone

zeroge · #9 (**permalink**) 01-01-10, 13:58

r937...

are we talking the same language? How does my code look at your side ??? CHINESE? It looks pretty much English to me and although I am in China, do I need to be CHINESE and even if I were...

Never mind. This seems to be the wrong forum anyway

Happy New Year

r937 · #10 (**permalink**) 01-01-10, 14:04

my apologies, what an unfortunate coincidence

i ~meant~ to say that your php code might as well be swahili

i.e. it is not germane to the mysql problem

and if you're not gonna do the SHOW CREATE TABLE for us, then we can't help you, eh

shammat · #11 (**permalink**) 01-01-10, 14:11

Quote:

Originally Posted by zeroge

It looks pretty much English to me and although I am in China, do I need to be CHINESE and even if I were...

No. It looks like PHP mixed with some HTML but this is a SQL forum, so you should only be posting SQL statements. That's what r937 meant.

You wouldn't post a chinese question into an english speaking forum either?

When trying to figure out a problem with your SQL query, run your query as pure SQL, not embedded into anything else (this is true for [i]any[i] scrip or program, not just PHP or HTML)

zeroge · #12 (**permalink**) 01-01-10, 14:18

I am really stuck... I don't understand what you mean with "SHOW CREATE TABLE"

The two pieces of code which I wrapped in quotes in my original post is all I have. the shorter bit is on the "search.php and the longer bit is the "results.php.

There is nothing else on these 2 pages.

When opening the search.php in the browser (uploaded to server) and selecting a choice from the "search.php and press the submit button, the code of the "results.php" should send the SQL to MySQL and return any result - if there is

I may be a bit slow (have worked 68 hrs nonstop and it is now - AGAIN 3:15 am but despite me trying to read you, I am really not getting what you mean... sorry

zeroge · #13 (**permalink**) 01-01-10, 14:24

My apology for being in the wrong forum. I just thought since this is connected with SQL ...?

So you may as well close this post. I don't want to appear like an intruder or else waste your time or annoy others.

SORRY for walking through the wrong door

r937 · #14 (**permalink**) 01-01-10, 14:44

Quote:

Originally Posted by zeroge

I am really stuck... I don't understand what you mean with "SHOW CREATE TABLE"

i believe you have used phpmyadmin?

go to the SQL tab and run the following SQL statement --

Code:

SHOW CREATE TABLE clients

however, at this point the whole unique business is probably moot, since you seem to be having php problems, not mysql problems

you might want to try the php forum at sitepoint.com/forums/ -- they have some really sharp php guys

zeroge · #15 (**permalink**) 01-01-10, 15:02

SQL result

Host: myhost
Database: mydatabase
Generation Time: Jan 01, 2010 at 08:03 PM
Generated by: phpMyAdmin 3.2.4 / MySQL 5.0.87-community
SQL query: SHOW CREATE TABLE clients;
Rows: 1
Table Create Table
clients CREATE TABLE `clients` (
`first_name` varchar(30...

Internet Services

Web Development

Digital Marketing

Consumer Tech