Click here to Skip to main content
12,404,251 members (77,672 online)
Rate this:
 
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);*/
}
?>
Posted 30-Mar-13 0:25am
Updated 30-Mar-13 1:22am
CPallini386.6K
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();
?>
karthikh87 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
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 1 Apr 2013
Copyright © CodeProject, 1999-2016
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