retrieve data from mysql using a search field in wordpress

dragonfx76 · December 1, 2016

Hello all.

First let me explain what the final result should be :-)

I want a page with nothing more then a search field. People can give a number in here, and that number will show them, depending on how much hits a result about let's say "ItemAA" has "itemBB" it's possible they have more "itemAA" matches with the number they give. The number is person related, so no person can have the same numbers.

Now i used a plugin to create a database of this (a crm tool) where we can export and import using excel files. There is a contact form attached to it where some1 can enter data without using the excel cheat.

What's ive got so far:

<html>
<body>

<form method="post">
    <input type="text" id="zoeknummer" name="zoeknummer"/>
    <input type="submit" name="Zoek" value="Zoek" onclick="" />
</form>




<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if ($_POST["zoeknummer"] != ""){
    echo"test <br>";
   $dbhost = 'localhost';
   $dbuser = 'database user';
   $dbpass = 'database pass';
   
   $conn = mysql_connect($dbhost, $dbuser, $dbpass);
   
   if(! $conn ) {
      die('Kan geen connectie maken: ' . mysql_error());
   }
   
   $sql = 'SELECT lead_content FROM wp_wgbsupicrm_leads';
   mysql_select_db('database name');
   $retval = mysql_query( $sql, $conn );
   
   if(! $retval ) {
      die('Kan geen gegevens vinden: ' . mysql_error());
   }
   


   while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
      echo "Resultaat:" . $row['lead_content'] . " <br> ".
         "--------------------------------<br>";
   }
   
   echo "Gegevens ontvangen\n";
   
   mysql_close($conn);
}?>

</body>

test 
Resultaat:{"zoeknummer":"554477","komplex":"test 4","plaats":"84","versturen":null} 
--------------------------------
Resultaat:{"zoeknummer":"556478","komplex":"test 3","plaats":"51","versturen":null} 
--------------------------------
Resultaat:{"zoeknummer":"112255","komplex":"test 2","plaats":"12","versturen":null} 
--------------------------------
Resultaat:{"zoeknummer":"110022","komplex":"Test 1","plaats":"1","versturen":null} 
--------------------------------
Gegevens ontvangen

What i'm missing and where it goes wrong.. it shows ALL data and i want that when a person enters the number what you see behind "zoeknummer": will result in an output of only "komplex": and "plaats"

i have no idea how to get there since this toke me almost 2 full day's to get to this point.. and i know it might be as simple as baking an egg.. if u don't know what an egg is that is also pretty hard ;-)

Any help would be appreciated. I'm trying to learn php and mysql here..

Edited December 1, 2016 by dragonfx76

Barand · December 1, 2016

When you pack all your data into a single column you give yourself no option other than select all the data. Construct you table correctly.

CREATE TABLE `lead` (
  `zoeknummer` int(11) NOT NULL,
  `komplex` varchar(45) DEFAULT NULL,
  `plaats` int(11) DEFAULT NULL,
  `versturen` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`zoeknummer`)
)

+------------+---------+--------+-----------+
| zoeknummer | komplex | plaats | versturen |
+------------+---------+--------+-----------+
|     110022 | test 1  |      1 |      NULL |
|     112255 | test 2  |     12 |      NULL |
|     554477 | test 4  |     84 |      NULL |
|     556478 | test 3  |     51 |      NULL |
+------------+---------+--------+-----------+

If you are just learning, learn PDO with you MySQL database and don't waste any more time with the mysql_ set of functions. These have been removed from PHP.

As you are just fetching data to display, use GET instead of POST. Use POST for updating data.

Your code would now be

if (isset($_GET['zoeknummer']))  {   
    $sql = "SELECT komplex
            , plaats
            FROM lead
            WHERE zoeknummer = ?";
    $stmt = $pdo->prepare($sql);     // where $pdo is your db connection
    $stmt->execute([ $_GET['zoeknummer'] ]);
    if ($row = $stmt->fetch()) {
        echo $row['komplex'] . ' : ' . $row['plaats'];
    }
}

dragonfx76 · December 1, 2016

Hello there, thanks for the fast reply.

Concerning the structure in the database, i'm aware this makes it alot harder, the thing is that i don't build the database, i use a plugin for wordpress where it just takes info from a excel cheat and makes leads from it on the website.

I'm afraid i have to work with the structure this pluging makes. No clue how to change it in the plugin it self.

I did change the POST to GET

What i did find so far is that i can 'explode' the field value being: {"zoeknummer":"554477","komplex":"test 4","plaats":"84","versturen":null} into arrays.. No idea how to, and no idea what it mean. I understand it makes the value into different arrays where i then can say if array (where the zoeknummer would be) = 'value entered in the search field' then 'echo' array with value komplex & plaats..

Hope this does make any sence i typed here.. trying to explain something i don't really get is pretty hard.

I appreciate all help given, and i would change the database if i know how to and if it still would be working with the plugin then..

ps the change from POST to GET gave an error..

Edited December 1, 2016 by dragonfx76

Barand · December 1, 2016

The content data is JSON encoded, so you need to decode it then access the content fields

if (isset($_GET['zoeknummer']))  { 
  
    $sql = "SELECT lead_content
            FROM lead";
    $res = $pdo->query($sql);     // where $pdo is your db connection
    
    while  ($lead = $res->fetchColumn()) {

        $data = json_decode($lead);
        if ($data->zoeknummer == $_GET['zoeknummer']) {
            echo $data->komplex . ' : ' . $data->plaats . '<br>';
        }
        
    }
}

Edited December 1, 2016 by Barand

Barand · December 1, 2016

ps the change from POST to GET gave an error

Did you change the form method to GET also?

dragonfx76 · December 1, 2016

Hey there, again thanks for the fast reply..

I used the code, it doesn't give an error, but it also doesn't show results.

dragonfx76 · December 1, 2016

Did you change the form method to GET also?

Notice: Undefined variable: GET in /home/sbgw/public_html/test2.php on line 17

<form method="GET">
    <input type="text" id="zoeknummer" name="zoeknummer"/>
    <input type="submit" name="Zoek" value="Zoek" onclick="" />
</form>

if ($GET["zoeknummer"] != ""){
    echo"test <br>";

dragonfx76 · December 1, 2016

I use the script one 2 pages is it safe to share a link here?

Barand · December 1, 2016

Did you create a PDO connection instead of the mysql_connect()?

    $pdo = new PDO("mysql:host=".HOST.";dbname=".DBNAME,USERNAME,PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

dragonfx76 · December 1, 2016

so i should have this?

<html>
<body>

<form method="get">
    <input type="text" id="zoeknummer" name="zoeknummer"/>
    <input type="submit" name="Zoek" value="Zoek" onclick="" />
</form>




<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

if (isset($_GET['zoeknummer']))  {

    $pdo = new PDO("mysql:host=".localhost.";dbname=".DATABASENAME,USERNAME,PASSWORD);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);


    $sql = "SELECT lead_content
            FROM lead";
    $res = $pdo->query($sql);     // where $pdo is your db connection
    
    while  ($lead = $res->fetchColumn()) {

        $data = json_decode($lead);
        if ($data->zoeknummer == $_GET['zoeknummer']) {
            echo $data->komplex . ' : ' . $data->plaats . '<br>';
        }     
    }
}?>

</body>

Barand · December 1, 2016

You need to provide your db credentials in the first $pdo line (database, username and password)

$dbhost = 'localhost';
$dbuser = '****';
$dbpass = '****';
$database = '****';

    $pdo = new PDO("mysql:host=$dbhost; dbname=$database", $dbuser, $dbpass);

Edited December 1, 2016 by Barand

dragonfx76 · December 5, 2016

Great thanks, it's working perfect!!

Should learn more about phph and sql myself, it's planned. But it seems all so complicated o_O

dragonfx76 · December 7, 2016

Hi there,

I hope i can ask something more about this script. It works wonderfull if i upload it to the website as a seperate page.

Now i used the code in a wordpress site and there it either breaks the code up, or it returns to the home page.

Also, when using this code, is there a way people can see the password a username etc?

Sign In

retrieve data from mysql using a search field in wordpress

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information