Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51

    Unanswered: PHP form to query DB for ANY entry

    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...
    <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)

    <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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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
    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;
    This is the script which will display the results
    So I am really confused why you say "nobody can help if I show nothing"

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    ??? 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 View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    Hello Healdem

    Thanks for your reply..
    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

  9. #9
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by zeroge View Post
    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)

  12. #12
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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

  13. #13
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by zeroge View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Dec 2009
    Location
    Now in China and since the past 7 years in China
    Posts
    51
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •