sql php results and search

Question

I have a problem, small to others, but huge to me. I have been working on a project since March 15 of this year. I am not a web designer but this is just a hobby of mine.

My problems are:

When I call this program for data, I receive records but it only works if I search for the full postcode (EX 1: n = no results EX 2: nn12ab = 5 results displayed )
I have to arrange the results in some order (my results = abcdabcdabcdabcdnn12ababcdabcdabcdabcdnn12ababcdabcdabcdabcdnn12ab, the way I am trying to get them its first name / last name / email / postcode.

I had checked in w3schools and all other mode but still I am asking this. :( I am fully aware its no hack protected , I just want to make it work. any idea where I need to place whatever works ? TXT IN ADVANCE! HTML search

<form method="post" action="search.php">
    <center>
      <h1>My Search Engine</h1>
      <input type="text" value="Search..." name="query" />
      <input type="submit" value="Find" name="list" />
    </center>
  </form>

PHP SEARCH and display CODE

<?php
$servername = "localhost";
$username = "abcd";
$password = "******";
$dbname = "abcd";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT * FROM wfuk";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table><td><tr><th>ID</th></td></tr>
    <th>Name</th></td></tr>
    <th>postcode</th</td>></tr>
    <th>trade</th></td></tr>
    <th>telephone</th></td></tr>
    <th>comments</th></td></tr></table>
    ";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "<table><tr><td>"
         .$row["id"].
         "</td><td>"
         .$row["first_name"]
         .$row["last_name"].
         "</td></tr>".

         "<tr><td>"
         .$row["post_code"].
         "</td></tr>".

         "<tr><td>"
         .$row["trade"].
         "</td></tr>".

         "<tr><td>"
         .$row["telephone"].
         "</td></tr>".

         "<tr><td>"
        .$row["comments"].
        "</td></tr></table>"
        ;
    }
    echo "</table>";
} else {
    echo "0 results";
}
$conn->close();
?>

Please don't dump code in comments. Edit your original post to add any new information. — Jay Blanchard
– Jay Blanchard, Commented Jul 14, 2015 at 19:42
You're not doing anything to filter/limit results from the query. — Jay Blanchard
– Jay Blanchard, Commented Jul 14, 2015 at 19:44
use 'like' clause in your query, and put border for your table , problem solved :) cheers — captain_a
– captain_a, Commented Jul 14, 2015 at 20:09

Nelson Teixeira · Accepted Answer · 2015-07-14 19:58:31Z

1

Substitute this line:

$sql = "SELECT * FROM wfuk";

by

$sql = "SELECT * FROM wfuk where name like " . $_POST["query"] . " order by  first_name, last_name, email, postcode";

I'm assuming that the columns in table wfuk have the names you said. If not, change them by the column names.

This is not the best way to do a search, because it open the possibility for SQL-injection attacks. But at your current level of knowledge you probably aren't ready for other solution. Later please educate yourself on better prattices on this kind of operation.

answered Jul 14, 2015 at 19:58

Nelson Teixeira

6,6366 gold badges46 silver badges90 bronze badges

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

Comments

captain_a · Accepted Answer · 2015-07-14 20:07:14Z

1

Nothing to worry about, just basic confusions . Answer of first question: Dont use = sign in query like this :

Select * from table where postcode='.$variable.'

Use like clause this :

Select * from table where postcode like '%.$variable.%'

Answer for Second question:

Place border for your table :

<table border="1">

answered Jul 14, 2015 at 20:07

captain_a

3,2871 gold badge16 silver badges24 bronze badges

Comments

Zaffar Saffee · Accepted Answer · 2015-07-14 19:44:45Z

0

a few things here

Use some good tutorials, don't trust on w3school (some people call it w3fool)
Never User Select * from table, rather specify column names something like Select firstname, lastname from table
if you want search based on integer, user = sign e.g where rollunme=134
if you want to search some text/ character field , use LIKE operator eg firstname LIKE %zaffar%

these are basic tips which should help you...

PS

question edited, but these tips should still apply as they are very generic in nature and should help you

answered Jul 14, 2015 at 19:44

Zaffar Saffee

6,3056 gold badges42 silver badges78 bronze badges

Comments

Remus Afrem · Accepted Answer · 2015-07-14 23:15:03Z

yes it work unfortunately not whit this code, but from hear i lear the pice that i was missing THX ALL . CODE I HAVE USE

<?php 
//load database connection
    $host = "localhost";
    $user = "change my";
    $password = "change my";
    $database_name = "chage my database name";
    $pdo = new PDO("mysql:host=$host;dbname=$database_name", $user, $password, array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ));
// Search from MySQL database table
$search=$_POST['search'];
$query = $pdo->prepare("select * from change_table_name where change_title LIKE '%$search%' OR change_author LIKE '%$search%'  LIMIT 0 , 10");
$query->bindValue(1, "%$search%", PDO::PARAM_STR);
$query->execute();
// Display search result
         if (!$query->rowCount() == 0) {
		 		echo "Search found :<br/>";
				echo "<table style=\"font-family:arial;color:#333333;\">";	
           // if need to multiply check clousley <tr> and </td> make shure they are on the right order
                echo "<tr>
                <td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Change_Title_Books</td>
                <td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">Change_Author</td>
                <td style=\"border-style:solid;border-width:1px;border-color:#98bf21;background:#98bf21;\">change_Price</td></tr>";				
            while ($results = $query->fetch()) {
              // if need to multiply check clousley <tr> and </td> make shure they are on the right order
				echo "<tr><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";			
                echo $results['Chage_title'];
				echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
                echo $results['Change_author'];
				echo "</td><td style=\"border-style:solid;border-width:1px;border-color:#98bf21;\">";
              // if not needit delete "$". from bellow
                echo "$".$results['change_price'];
				echo "</td></tr>";				
            }
				echo "</table>";		
        } else {
            echo 'Nothing found';
        }
?>

<html>
<head>
<title> How To Create A Database Search With MySQL & PHP Script | Tutorial.World.Edu </title>
</head>
<body>
<form action="search-database.php" method="post">
Search: <input type="text" name="search" placeholder=" Search here ... "/>
<input type="submit" value="Submit" />
</form>
<p><a href="http://tutorial.world.edu/web-development/how-to-create-database-search-mysql-php-script/">PHP MySQL Database Search</a> by <a href="http://tutorial.world.edu">Tutorial.World.Edu</a></p>
</body>
</html>

i found a different code i will post it for future references but you guys let me understand the thinks i could not understand

Collectives™ on Stack Overflow

sql php results and search

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related