How can I make a script retrieve specific data from a MYSQL database?

Question

I made a simple search to retrieve customer data from a database. So far I've only been able to make it successful display the current table data on the webpage but I can't get the search function to retrieve specific data. I'm having 0 database access issues.

I've been searching on goggle for hours and trying many different combinations but I can't find any decent tutorials on making a mysqli search.

Link to my current php page

Cust_no is primary key(varchar), prog_id(integer), balance(varchar)

 **Table structure**

Query Output:

> SELECT prog_id, cust_no, balance

FROM `tee`.`teecust`

+ ------------ + ------------ + ------------ +

| prog_id      | cust_no      | balance      |

+ ------------ + ------------ + ------------ +

| 220852       | 1184631      | 0
           |
| 220853       | 1184693      | 0
           |
| 225726       | 1186292      | 0
           |
| 220854       | 1233446      | 0
           |
| 220856       | 1233672      | 0


<!DOCTYPE html>
        <head>
            <title>Search the Database</title>
        </head>



  <body>
      <form action="search.php" method="get" enctype="application/x-www-form-urlencoded" target="_self" id="search">
          Search: <input type="text" name="term" /><br />
          <input type="submit" name="submit" value="Submit" />
    </form>

 <?php

    $host="****";
    $port="3306";
    $socket="";
    $user="u*****";
    $password="*****";
    $dbname="tee";

    $mysqli = new mysqli($host, $user, $password, $dbname);
        if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }
    $query = "SELECT cust_no FROM teecust LIMIT 20,5";

    if ($result = $mysqli->query($query)) {
        /* fetch associative array */

     while ($row = $result->fetch_row()) {
            printf ("Customer Number:%s   Billing ID:%s   Balance due:%s\n", $row["cust_no"], $row["prog_id"], $row["balance"]);
    }
        /* free result set */
        $result->close();
    }

    /* close connection */
    $mysqli->close();
    ?>

        </body>
    </html>

You need a more complex SQL query, specifically, you'll need to learn about the WHERE clause: w3schools.com/sql/sql_where.asp — Nick Pickering
– Nick Pickering, Commented Mar 31, 2013 at 3:13
Hello @WillWIlkinsIII !As i can see when i access the link that you have provided to your page , this warning : Unknown column 'term' in 'where clause' so i could guess that your sql is malconstructed so can you please tell me if the code posted here is the same used in the page you point to and if it is the same why in this code we could not find neither a where statement neither a term word so if the both are different so can you please explain what is the relation of your code posted here with the page you have provided a link to it ? So we can be more in the environement of your problem :) — Last Breath
– Last Breath, Commented Mar 31, 2013 at 4:29

What have you tried · Accepted Answer · 2013-03-31 05:29:12Z

1

You need to get the search criteria via get - since that is what you specified. Although you didn't mention this in your post, I'm assuming you want to use the search criteria in your query:

Extra note: You may also want to read up on the where clause in SQL

(I've shown you how to use prepared statements for added security)

    <?php
            $searchTerm = $_GET['term']; // GET the search term submitted by the form

            $customerNumber = '';
            $progID = '';
            $balance = '';

            // build your query - notice the user of '?' as I'm using prepared statements to query the database. Also notice the 'where' clause.
            $sql = "Select cust_no, prog_id, balance From teecust where cust_no = ?";

            // Create the prepared statement
            if($stmt = $mysqli->prepare($sql)){
                $stmt->bind_param("s", $searchTerm); // bind the search term to the query where the '?' once was
                if(!$stmt->execute()){ // run the query
    echo $mysqli->error;
    }
                $stmt->bind_result($customerNumber, $progID, $balance ); // retrieve results into variables
                while($stmt->fetch()){ // loop through results
                    // You now have the result in $customerNumber, $progID, and $balance - do what    you want with it
echo "customer number: " . $customerNumber . "<br> Program ID: " . $progID . "<br> Balance: " . $balance;
                }
                $stmt->close();
            }
        else{
        echo $mysqli->error;
        }

            ?>

edited Mar 31, 2013 at 5:29

answered Mar 31, 2013 at 3:14

What have you tried

11.2k4 gold badges35 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

19 Comments

deek Over a year ago

I made some changes but I still get nothing: $customerNumber = ''; $progID = ''; $balance = ''; $sql = "Select cust_no, prog_id, balance From teecust where term LIKE '%".$searchTerm."%'";

What have you tried Over a year ago

@WillWIlkinsIII I don't understand the issue. could you please try to explain it in other terms?

What have you tried Over a year ago

@WillWIlkinsIII Are you still getting the MYSQL error? If not, the table is fine and so is the query. Notice that in the answer I posted I am not displaying any information, if you want I can add that to the code.

What have you tried Over a year ago

You're welcome - I just went through all your questions and upvoted so you would have enough reputation to chat - guess it was perfect timing :p Take care, good luck.

What have you tried Over a year ago

@YourCommonSense There is nothing wrong with mysqli - ... all preference. Also, I think it's a good way to learn some basics.

|

Community · Accepted Answer · 2017-05-23 11:57:27Z

1

There is nothing wrong with mysqli - ... all preference.

Surely it's a matter of preference. If one prefers bloated, non-reusable and insecure code, mysqli is their choice. But if they want something otherwise, then PDO have to be chosen:

$sql = "Select cust_no, prog_id, balance From teecust where cust_no = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute(array($_GET['term']));
while($row = $stmt->fetch()) {
    echo "Customer: $row[cust_no]<br>
          Program ID: $row[prog_id]<br>
          Balance: $row[balance]";
}

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Mar 31, 2013 at 6:04

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

1 Comment

What have you tried Over a year ago

Are you really trying to make the argument that mysqli prepared statements are insecure? As long as you handle all of the data correctly there is nothing insecure about using prepared statements.

Collectives™ on Stack Overflow

How can I make a script retrieve specific data from a MYSQL database?

2 Answers 2

19 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

19 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related