Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: PHP MySQL
Hi friends i m new to php and mysql and i want to execute a query where it has to search in 4 columns of "common" table(common is a table name). where we will be having only one textbox at the front end and its name="refno"
 
<?php
error_reporting(0);
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db("newcrm", $con);
 
$refid = "";
 
$tblname = "";
 
if(isset($_POST["submit1"]))
{
    $refid = $_POST['refno'];/*
    $pieces = explode("-", $tblname);*/
}
?>
 

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <title>abc</title>
  <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
  <link href="css/style.css" rel="stylesheet" type="text/css">
 
<script type="text/javascript">
// Popup window code
function newPopup(url) {
    popupWindow = window.open(
        url,'popUpWindow','height=700,width=800,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
}
</script>
 
<script type="text/javascript">
// Popup window code
function newPopup(url) {
    popupWindow = window.open(
        url,'popUpWindow','height=700,width=800,left=10,top=10,resizable=yes,scrollbars=yes,toolbar=yes,menubar=no,location=no,directories=no,status=yes')
}
</script>
 
<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.2/themes/smoothness/jquery-ui.css" />
  <script src="http://code.jquery.com/jquery-1.9.1.js"></script>
  <script src="http://code.jquery.com/ui/1.10.2/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css" />
  <script>
  $(function() {
    $( "#from" ).datepicker({
      defaultDate: "+1w",
      changeMonth: true,
      numberOfMonths: 1,
      onClose: function( selectedDate ) {
        $( "#to" ).datepicker( "option", "minDate", selectedDate );
      }
    });
    $( "#to" ).datepicker({
      defaultDate: "+1w",
      changeMonth: true,
      numberOfMonths: 1,
      onClose: function( selectedDate ) {
        $( "#from" ).datepicker( "option", "maxDate", selectedDate );
      }
    });
  });
  </script>
 
<style>
.ddtbl{
    width:100%;
    border-collapse:collapse;
}
.ddtbl td{
    border:1px solid red;
}
.ddtbl th{
    border:1px solid red;
}
 

 
</style>
</head>
<body>
<div class="main">
<div class="page">
  <!--<div class="header_nav">welcome <strong><?php echo $_SESSION['SESS_FIRST_NAME']." ".$_SESSION['SESS_LAST_NAME']; ?></strong> | <a href="logout.php">Sign Out </a>| Help </div>
-->
<div class="header">
<div class="banner">
  <h1><a href ="userform.php"> <img src="images/abc.png" width="100"alt="abc" align="absmiddle"/>abc</a></h1>
</div>
 
</div>
<div class="content">
<div class="content-in">
 
<div class="gap"></div>
<div class="right-panel" style="border:1px solid red;">
 
<div style="position:relative;overflow:hidden;width:100%;border:1px solid blue;min-height:200px;">
<form action="fetchdata.php" method="post">
 
<table style="width:100%;">
    <tr>
        <td>
            <div class="reg_form_row">
            <div class="reg_form_row_left">Enter RefNo. or Agent Name or abc No. or Caller Id</div>
            <div class="reg_form_row_right"><input name="refno" style="width: 150px;" maxlength="100" value="<?php echo $refid; ?>" /></div>
            </div>
        </td>
    </tr>
</table>
<input name="submit1" type="submit" value="Get Info" />
 
</form>
 
</div>
</div>
 

<?php
if(isset($_POST["submit1"]))
{
    if($refid!="")
    {
        $strquery = "select * from common where refno='$refid'" ;
        //$strquery = "select * from common where agentname='$refid'" ;
        //$strquery = "select * from common where zcno='$refid'" ;
        //$strquery = "select * from common where callerid='$refid'" ;
 
        $result = mysql_query($strquery);
 
        ?>
        <table class="ddtbl">
        <tr>
            <th>Results for Refno entry</th>
        </tr>
        </table>
        <table class="ddtbl">
        <tr>
            <th>refno</th>
            <th>date</th>
            <th>agentname</th>
            <th>zcno</th>
            <th>callerid</th>
        </tr>
        <?php
        while($data = mysql_fetch_array($result))
        {
            $refno = $data["refno"];
            $date = $data["date"];
            $agentname = $data["agentname"];
            $zcno = $data["zcno"];
            $callerid = $data["callerid"];
 
            ?>
 

        <tr>
            <td><?php echo $refno; ?></td>
            <td><?php echo $date; ?></td>
            <td><?php echo $agentname; ?></td>
            <td><?php echo $zcno; ?></td>
            <td><?php echo $callerid; ?></td>
        </tr>
 
            <?php
        }
        echo("</table><br /><br />");
    }
        $result1 = mysql_query($strquery1);
        ?>
 
        <?php
    }
?>
 
</body>
</html>
Posted 30-Mar-13 1:25am
Edited 30-Mar-13 2:22am
CPallini325.7K
v3
Comments
Prasad Khandekar at 30-Mar-13 15:27pm
   
Please elaborate the problem.
karthikh87 at 31-Mar-13 9:10am
   
HiPrasad below are the details and thanks for the response pls let me know the code for the below details.
 
DB Name: newcrm
table name: common
table columns: refno(primary key); date; agentname; zcno; callerid;
 
i want to retrieve data from common table, from refno or date or agentname or zcno or callerid.
 
php form contains only one text field and what ever client enters it has to search in all the mentioned above columns and get the data from the table.
 
php form textbox name is: "search"
Prasad Khandekar at 31-Mar-13 11:10am
   
Don't do this unless there is no option. For it to work correctly you need to ensure the correct data types, remember your table has a date column. I am assuming that zcno & callerid are all string columns.
 
On server side you will first check the value entered by user if it's a date then your SQL becomes SELECT * FROM common WHERE data >= 'yyyy-MM-dd' Where yyyy-MM-dd is the date entered by the user. For other values your SQL will be SELECT * FROM common WHERE refno = 'USERVAL' OR agentname = 'USERVAL' OR zcno = 'USERVAL' OR callerid = 'USERVAL'.
 
Regards,
karthikh87 at 31-Mar-13 11:16am
   
Thanks for the update so soon, and can u pls give me a code optimized for this i mean i am new to php and dono how to do as i did insertion option but not able to retrieve data.. from past week i m serching how ot retrieve data from php form pls help me thanks in advance.
Prasad Khandekar at 31-Mar-13 14:31pm
   
Hello Karthik,
 
Please find below the bare minimum code for querying the common table using user input. You need PHP 5.3.0 & above.
 
<?php function isDate($strDate, $format)
{
if (!isset($strDate) || !isSet($format)) return false;
 
$inDate = trim($strDate);
$val = date_create_from_format($format, $inDate);
return ($val-?-->format($format) == $inDate);
}
 
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
 
/* check connection */
if (mysqli_connect_errno()) {
die("Connect failed: %s\n", mysqli_connect_error());
}
 
$stmt = NULL;
$userIn = $_POST['search'];
if (isDate($userIn, "d/m/Y") {
$stmt = $mysqli->prepare("SELECT * FROM common WHERE date = ?")
$stmt->bind_param($userIn);
} else {
$stmt = $mysqli->prepare("SELECT * FROM common WHERE refno = ? OR agentname = ? OR zcno = ? OR callerid = ?");
$stmt->bind_param($userIn, $userIn, $userIn, $userIn);
}
$stmt->execute();
$result = $stmt->get_result();
?>
karthikh87 at 1-Apr-13 1:36am
   
Thanks Prasad.. i got query executed and i tired in different way and its working fine..

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Prasad Khandekar - Thanks and i submit this to make this as answered in the status update.
Hello Karthik,
 
Please find below the bare minimum code for querying the common table using user input. You need PHP 5.3.0 & above.
 
{
if (!isset($strDate) || !isSet($format)) return false;
 
$inDate = trim($strDate);
$val = date_create_from_format($format, $inDate);
return ($val-?-->format($format) == $inDate);
}
 
$mysqli = new mysqli('localhost', 'my_user', 'my_password', 'world');
 
/* check connection */
if (mysqli_connect_errno()) {
die("Connect failed: %s\n", mysqli_connect_error());
}
 
$stmt = NULL;
$userIn = $_POST['search'];
if (isDate($userIn, "d/m/Y") {
$stmt = $mysqli->prepare("SELECT * FROM common WHERE date = ?")
$stmt->bind_param($userIn);
} else {
$stmt = $mysqli->prepare("SELECT * FROM common WHERE refno = ? OR agentname = ? OR zcno = ? OR callerid = ?");
$stmt->bind_param($userIn, $userIn, $userIn, $userIn);
}
$stmt->execute();
$result = $stmt->get_result();
?>
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Mathew Soji 274
1 BillWoodruff 270
2 Afzaal Ahmad Zeeshan 244
3 Sergey Alexandrovich Kryukov 240
4 Raul Iloc 160
0 OriginalGriff 6,219
1 Sergey Alexandrovich Kryukov 5,853
2 DamithSL 5,103
3 Manas Bhardwaj 4,549
4 Maciej Los 3,845


Advertise | Privacy | Mobile
Web02 | 2.8.1411019.1 | Last Updated 1 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100