how to run multiple queries with or statement

Question

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 30-Mar-13 0:25am

karthikh87

Updated 30-Mar-13 1:22am

CPallini

v3

Add a Solution

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();
?>

karthikh87 1-Apr-13 1:36am

Thanks Prasad.. i got query executed and i tired in different way and its working fine..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

karthikh87 · Accepted Answer · 2013-03-31T19:38:00

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();
?>