Issue
Project- Database name- test2
i have 2 tables in my database
table1 contains
Id Name Mobile Email
17 name1 1111 name1@test
18 name2 2222 name2@test
28 name3 3333 name3@test
table2 contains
Sr. Id Paymobile Month Amount Date
1 28 3333 Jan 200 2021-01-06
2 28 3333 Feb 400 2021-02-06
3 28 3333 Apr 600 2021-04-08
4 17 1111 Mar 400 2021-03-05
6 18 2222 Aug 100 2021-08-27
7 17 1111 Jun 600 2021-06-21
table1 has single and unique records, table2 has include payment history so the id and mobile have multiple times.
What i Want If i search the mobile on or email id then it will also give the results in two tables like 1st table show ID,Name,Mobile,Email from table1 AND 2nd table show Month,Amount,Date from table2. all is good but there is one issue which is mention below.
Issue when i search the mobile no. 3333 or 2222 in my webpage then it show both results in two table as i want like table1 show Id,Name,Mobile,Email from table1 AND 2nd table show Month,Amount,Date from table2.
But the Main Problem is that it will show Id,Name,Mobile,Email many times although it have single records in my database. like enter image description here
My Configuration
1-form.php
<!DOCTYPE html>
<html>
<body>
<!-- (A) SEARCH FORM -->
<form method="post" action="1-form.php">
<h1>SEARCH FOR USERS</h1>
<input type="text" name="search" required/>
<input type="submit" value="Search"/>
</form>
<table>
<style>
table { font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
<tr>
<th>Id</th>
<th>Name</th>
<th>Mobile</th>
<th>Email</th>
</tr>
<?php
// (B2) DISPLAY RESULTS
?>
<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST["search"])) {
// (B1) SEARCH FOR USERS
require "2-search.php";
?>
<?php
// (B2) DISPLAY RESULTS
if(isset($_POST) && array_key_exists('search',$_POST)) {
if (count($results) > 0) { foreach ($results as $r) {
?>
<tr>
<td> <?php echo $r['Id'] ;?> </td>
<td> <?php echo $r['Name'] ;?> </td>
<td> <?php echo $r['Mobile'] ;?> </td>
<td> <?php echo $r['Email'] ;?> </td>
</tr>
<?php
}
}
} else {
echo "No results found"; }
}
?>
</table>
<p>
<table>
<tr>
<th>Month</th>
<th>Amount</th>
<th>Date</th>
</tr>
<?php
// (B2) DISPLAY RESULTS
if (count($results) > 0) {
foreach ($results as $r) {
?>
<tr>
<td> <?php echo $r['Month'] ;?> </td>
<td> <?php echo $r['Amount'] ;?> </td>
<td> <?php echo $r['Date'] ;?> </td>
</tr>
<?php
}
} else {
echo "No results found";
}
?>
</table>
</html>
and
2-search.php
<?php
// (A) DATABASE CONFIG - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test2");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (B) CONNECT TO DATABASE
try {
$pdo = new PDO(
"mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
} catch (Exception $ex) {
exit($ex->getMessage());
}
// (C) SEARCH
$stmt = $pdo->prepare("SELECT table1.*, table2.*
FROM table1
INNER JOIN table2 ON table1.Id = table2.Id
WHERE `Name` LIKE ? OR `Mobile` LIKE ?");
$stmt->execute(["%".$_POST["search"]."%", "%".$_POST["search"]."%"]);
$results = $stmt->fetchAll();
if (isset($_POST["ajax"])) {
echo json_encode($results);
}
Solution
By mixing multiple steps and method by the help of stack over flow and w3school.
Now my New Configuration is listed below
1-form.php
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
</style>
<!DOCTYPE html>
<html>
<body>
<!-- (A) SEARCH FORM -->
<form method="post" action="1-form.php">
<h1>SEARCH FOR USERS</h1>
<input type="text" name="search" required/>
<input type="submit" value="Search"/>
</form>
<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST["search"])) {
// (B1) SEARCH FOR USERS
require "2-search.php";
?>
<table>
<tr>
<th>Id</th>
<th>Name</th>
<th>Mobile</th>
<th>Email</th>
</tr>
<?php
// (B2) DISPLAY RESULTS
if (count($results) > 0) { foreach ($results as $r) {
?>
<tr>
<td> <?php echo $r['Id'] ;?> </td>
<td> <?php echo $r['Name'] ;?> </td>
<td> <?php echo $r['Mobile'] ;?> </td>
<td> <?php echo $r['Email'] ;?> </td>
</tr>
<?php
}} else { echo "No results found"; }
}
?>
</table>
<p>
Billing Details
<p>
<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST["search"])) {
// (B1) SEARCH FOR USERS
?>
<table>
<tr>
<th>Id</th>
<th>Name</th>
<th>Mobile</th>
<th>Email</th>
</tr>
<?php
// (B2) DISPLAY RESULTS
if (count($results2) > 0) { foreach ($results2 as $r2) {
?>
<tr>
<td> <?php echo $r2['Id'] ;?> </td>
<td> <?php echo $r2['Month'] ;?> </td>
<td> <?php echo $r2['Amount'] ;?> </td>
<td> <?php echo $r2['Date'] ;?> </td>
</tr>
<?php
}} else { echo "No results found"; }
}
?>
</table>
</body>
</html>
2-search.php
<?php
// (A) DATABASE CONFIG - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test2");
define("DB_CHARSET", "utf8");
define("DB_USER", "root");
define("DB_PASSWORD", "");
// (B) CONNECT TO DATABASE
try {
$pdo = new PDO(
"mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
} catch (Exception $ex) { exit($ex->getMessage()); }
// (C) SEARCH
$stmt = $pdo->prepare("SELECT * FROM `table1` WHERE `Id` LIKE ? OR `Mobile` LIKE ?");
$stmt->execute(["%".$_POST["search"]."%", "%".$_POST["search"]."%"]);
$results = $stmt->fetchAll();
if (isset($_POST["ajax"])) { echo json_encode($results); }
$pdo->connection = null;
$pdo=null;
try {
$pdo2 = new PDO(
"mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
DB_USER, DB_PASSWORD, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
]
);
} catch (Exception $ex) { exit($ex->getMessage()); }
// (C) SEARCH
$stmt2 = $pdo2->prepare("SELECT * FROM `table2` WHERE `Id` LIKE ? OR `Paymobile` LIKE ?");
$stmt2->execute(["%".$_POST["search"]."%", "%".$_POST["search"]."%"]);
$results2 = $stmt2->fetchAll();
if (isset($_POST["ajax"])) { echo json_encode($results2); }
$pdo2->connection = null;
$pdo2=null;
Answered By - Naveen Tanwar
Posts
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.