Click here to Skip to main content
15,845,436 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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"

XML
<?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
Updated 30-Mar-13 2:22am
v3
Comments
Prasad Khandekar 30-Mar-13 15:27pm    
Please elaborate the problem.
karthikh87 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 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 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 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();
?>

1 solution

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();
?>
 
Share this answer
 
v2

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

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900