|
PHP - Posting To Mssql Based On Two Criteria.
This is posting to any row in mssql that has a matching 'start_date'. I would like it to post rows where start_date = '$start_date' and it also matches the 'meter_id' . It should perform this check of both parameters before posting to mssql.
<?php $session_id = $_SESSION['id']; $comm_id = $_GET['meter_id']; $start_date = $_GET['start_date']; $payment_date = $_POST['payment_date']; $amount_paid = $_POST['amount_paid']; $check_number = $_POST['check_number']; $sql = "UPDATE [radiogates].[dbo].[ops_invoice_history] SET payment_date = '$payment_date', amount_paid = '$amount_paid', check_number = '$check_number' where start_date = '$start_date'"; $query = sqlsrv_query($conn, $sql);Thank you for any help. Similar TutorialsThis topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=308916.0 Hey All. Say someone was searching my photo database website from a drop down menu. They search for the aircraft: 'Boeing 747' and airline: 'British Airways', which is posted with the url 'result.php?aircraft=Boeing+747&airline=British+Airways'. How would i get my php to echo: Your search for 'Boeing 747 > British Airways' is displayed below: Or if the search was for just: Aspect: Flightdeck, echo: Your search for 'Flightdeck' is displayed below: I hope it's clear, Thanks, Jimmy How to Store the search criteria like if user serach for a particular job and want to store that search ? Should i create anyother table to store critaria .... i have no idea what to do in this case/ thankx in advance I need a php/mysql star rating which has 3 criteria to rate and at the bottom a link when clicked saves the ratings. Any idea of where to get that? Something like this Rate criteria 1 Rate criteria 2 Rate criteria 3 Save ratings I am working on a project that involves providing details for county government, and if applicable, cities independent of county oversight. Virginia is a state with a lot of independent cities, so I wanted to echo them in a separate list underneath the county governments.
This code works fine when displaying Virginia information. However, I would like to hide the Independent Cities heading in every other state. For some reason, this code still echoes the Independent Cities heading in states without independant cities in the database.
Any thoughts or wisdom?
(sorry for pasting the whole bulky code)
$query = "SELECT * FROM state WHERE st_id=$statecode";
$result = mysql_query($query) or die("Query $query failed : " . mysql_error());
$row = mysql_fetch_assoc($result);
$state_name=$row["st_name"];
$state_note=$row["state_notes"];
echo "<h2>Counties of " . $state_name; "</h2>\n";
$query = "SELECT * FROM counties WHERE st_id=$statecode AND visible=1 ORDER BY county_name ASC";
$result = mysql_query($query) or die("Query $query failed : " . mysql_error());
echo "<table width=100%><tr><td valign=top width=33%>\n";
$count = 1;
$col = 0;
$rowcount = (int)(mysql_num_rows($result) / 3) + 1;
$remainder = mysql_num_rows($result) - ($rowcount - 1) * 3;
/* ********************************************************* */
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "<a href=\"services.php?county=".$row["county_id"]."\">".$row["county_name"] . "</style></a><br />\n";
$count = $count + 1;
if ((($remainder > 0) && ($count == ($rowcount + 1))) || (($remainder <= 0) && ($count == $rowcount))) {
$col = $col + 1;
if ($col!=3) {
echo "</td><td valign=top width=33%>\n";
$count = 1;
if ($remainder > 0)
$remainder = $remainder - 1;
}
}
}
/* ********************************************************* */
echo "</td></tr></table><br>\n";
mysql_free_result($result);
/* ********************************************************* */
$query2 = "SELECT * FROM cities WHERE st_id=$statecode AND visible=1 ORDER BY city_name ASC";
$result2= mysql_query($query2) or die("Query $query failed : " . mysql_error());
$city_id = $row['city_id'];
$city_name = $row['city_name'];
if ($city_id!="") {
echo "<h3>Independent cities of " . $state_name; "</h3>\n";
echo "<table width=100%><tr><td valign=top width=33%>\n";
$count2 = 1;
$col2 = 0;
$rowcount2 = (int)(mysql_num_rows($result2) / 3) + 1;
$remainder2 = mysql_num_rows($result2) - ($rowcount2 - 1) * 3;
/* ********************************************************* */
while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) {
echo "<a href=\"services.php?county=".$row["city_id"]."\">".$row["city_name"] . "</style></a><br />\n";
$count2 = $count2 + 1;
if ((($remainder2 > 0) && ($count2 == ($rowcount2 + 1))) || (($remainder2 <= 0) && ($count2 == $rowcount2))) {
$col2 = $col2 + 1;
if ($col2!=3) {
echo "</td><td valign=top width=33%>\n";
$count2 = 1;
if ($remainder2 > 0)
$remainder2 = $remainder2 - 1;
}
}
}
/* ********************************************************* */
echo "</td></tr></table><br>\n";
mysql_free_result($result2);
/* ********************************************************* */
}
?>
Edited by myotch, 06 September 2014 - 09:19 PM. So let's assume i'm making a script for searching cars by different criteria. Name of the car, color, and mileage. I used to do this by using LIKE and just adding AND for as many times as needed. But i've read that that is obsolete and is giving servers a hard time, so i went ahead and started making a new script that will use fulltext search. I've hit a dead end. Code: [Select] function search($table,$what,$string,$limit,$start,$country) { global $totrows; global $pages; $string = explode("*",$string); $what = explode("*",$what); $cname = trim($string[0]); $t = 0; $parameters = ""; while (isset($what[$t])){ if ($t>0) { $paramm .= ", "; } $paramm .= trim($what[$t]); if ($t>0) { $parama .= " "; } $parama .= trim($string[$t]); $t++; } $parameters .= " AND locationc = '".$country."'"; $sql = "SELECT *, MATCH(".$paramm.") AGAINST('".$parama."') AS score FROM main WHERE MATCH(".$paramm.") AGAINST('".$parama."') ORDER BY score DESC"; $result = mysql_query($sql) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { var_dump($row); } } How does this work: The user selects and enters his parameters> The script then joins them all into a single variable which is then sent to the function(the reason i do this i because the user might input only the name, so that way i can add more criteria and not need to change the script)> The script explodes those into arrays and then it processes them into a form for use with sql> I run the sql query and then return the results. What's the problem? There's two of them actually> The first one is that mysql returns an error "Can't find FULLTEXT index matching the column list" even though i did setup fulltext indexes. (phpmyadmin c/p) Code: [Select] name FULLTEXT No No name 0 YES color FULLTEXT No No color 0 YES mileage FULLTEXT No No mileage 0 YES The second problem is that the script is not selective and will not work as intended. For example, a car's name is 300, the users input's 300, and the script will return those rows that had mileage 300 or 300000 or whatever. How do i fix this, and is FULLTEXT the right way to go with multi criteria search? Hi,
I can I include a date range criteria to query with in the following code? The date field in the table (t_persons) is IncidentDate.
$criteria = array('FamilyName', 'FirstName', 'OtherNames', 'NRCNo', 'PassportNo', 'Gender', 'IncidenceCountryID', 'Status', 'OffenceKeyword', 'AgencyID', 'CountryID', 'IncidenceCountryID' );
$likes = "";
$url_criteria = '';
foreach ( $criteria AS $criterion ) {
if ( ! empty($_POST[$criterion]) ) {
$value = ($_POST[$criterion]);
$likes .= " AND `$criterion` LIKE '%$value%'";
$url_criteria .= '&'.$criterion.'='.htmlentities($_POST[$criterion]);
} elseif ( ! empty($_GET[$criterion]) ) {
$value = mysql_real_escape_string($_GET[$criterion]);
$likes .= " AND `$criterion` LIKE '%$value%'";
$url_criteria .= '&'.$criterion.'='.htmlentities($_GET[$criterion]);
} //var_dump($likes);
}
$sql = "SELECT * FROM t_persons WHERE PersonID>0" . $likes . " ORDER BY PersonID DESC";
Kind regards.
Hello, am having problem with connection to mssql server and database. Am using Xampp and i think there is some problem about his configuration. All the time am getting error : missing driver php_mssql.dll in xampp/php/ext/ ....and file php_mssql.dll is there (same problem with php_pdo_mssql.dll). Also am getting this error: "Fatal error: Call to undefined function mssql_connect() " I tried everything i could find on forums, blogs, and php documentation...but always same. btw... i removed ";" in php.ini from necessary lines and i copy ntwdblib.dll in necessary folders. This is my connection code: Code: [Select] $dbHost = 'Workstation:<port>'; $dbUser = 'user'; $dbPass = 'pass'; $dbName = 'database'; $con = mssql_connect ($dbHost, $dbUser, $dbPass) or die ('MsSQL connect failed. ' . mssql_error()); mssql_select_db($dbName) or die('Cannot select database. ' . mssql_error()); I also tried with: Code: [Select] $connection = odbc_connect("Driver={SQL Server Native Client 10.0};Server=$server;Database=$database;", $user, $password);but always same.... Can anybody please provide me some working solution ? Thanks. I'm running:
Windows Server 2003
IIS 6.0
Microsoft SQL 2005
PHP 5.3.28
Everything that i have read says, "5.3 got rid of mssql and now uses sqlsrv"
so i added extension=php_sqlsrv_53_nts_vc9.dll to my php.ini (and yes it is in the ext folder)
and when i run the script
$serverName = "localhost\phonebook"; //serverName\instanceName
// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"XXXXX", "UID"=>"XXXXX", "PWD"=>'XXXXXXXX');
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn ) {
echo "Connection established.<br />";
}else{
echo "Connection could not be established.<br />";
die( print_r( sqlsrv_errors(), true));
}
i get
Connection could not be established. Array ( [0] => Array ( [0] => IMSSP [SQLSTATE] => IMSSP [1] => -49 [code=auto:0] => -49 [2] => This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server. Neither of those ODBC Drivers are currently installed. Access the following URL to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver for x86: http://go.microsoft..../?LinkId=163712 [message] => This extension requires either the Microsoft SQL Server 2008 Native Client (SP1 or later) or the Microsoft SQL Server 2008 R2 Native Client ODBC Driver to communicate with SQL Server. Neither of those ODBC Drivers are currently installed. Access the following URL to download the Microsoft SQL Server 2008 R2 Native Client ODBC driver for x86: http://go.microsoft..../?LinkId=163712 ) [1] => Array ( [0] => IM002 [SQLSTATE] => IM002 [1] => 0 [code=auto:0] => 0 [2] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified [message] => [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified ) ) What can i do to fix this? I assume use an older .dll? HI, I've upgraded from PHP version 5.2 to PHP version 5.4 and are not able to load / find mssql extension ? How do I use MSSQL in version 5.4 ?? Is MSSQL not supported in this version or am I (hopingly) missing something ??? Regards Dieter Hi, I have been working with inserting unicode characters into mssql database from a php application. My php web application displays the chinese characters as i entered and retrieves from database properly. But when I take a look at the database the fields are populated with some other values other than the characters i inserted. When i developed the same application in ASP.net the database has correct values inserted into it. Here is my code in php: Code: [Select] <?php // direct insert $var = mssql_connect('RAL-DEV-SQL01','TestDBSa','TestDB$@'); $selected = mssql_select_db('TestDB', $var); $myVar="日常生活"; $myChineseVar=$myVar; echo $myChineseVar; $query = "insert into TestChinese (TestName) values (N'{$myChineseVar}')"; $result = mssql_query($query,$var); echo "Inserted<br>"; echo $query; echo "<br>Result<br>"; $result2 = mssql_query("SELECT TOP 10 * FROM [TestChinese] order by SeqNum desc",$var); while($nt=mssql_fetch_array($result2)){ echo "$nt[SeqNum] - "."$nt[TestName] -"; echo "<br>"; } mssql_close($var); ?> Can someone help me with this problem Thanks I'm having problems with enabling the sort functions of jqgrid. I think I've narrowed the problem down to the way MSSQL is returning the data to the SQL query. In an effort to troubleshoot, I've drastically simplified the problem. The query I'm using is below. Code: [Select] SELECT * FROM dbo.test ORDER BY locationName When I run the query directly in MSSQL Management Studio, the top 5 ID entries are 132, 1309, 1295, 1281, 1267. When I run the corresponding code in PHP, the top 5 ID entries are 1266, 1267, 1268, 1269, 1270 - it seems to be ignoring my order by clause. Is there something I need to do to force it to honor the ORDER BY locationName? Code: [Select] $kpiQuery = "SELECT * FROM dbo.test ORDER BY locationName"; $result = mssql_query($kpiQuery); while($kpiRow = mssql_fetch_array($kpiResult)) { echo $kpiRow[plDataID]."<br>"; } I've got some PHP code that accesses SQL on an Amazon cloud server. Other machines running Unix and Windows can perform accesses fine using the server name (IP for Windows, label for Unix), database name (string) and a password (string). However, the same code FAILS on OSX (my Macbook Pro). SQL managers on my MBP have no problem accessing it, since I've made the proper exceptions on the AWS firewall. But PHP can't do any MS SQL reads of this database. Is there something else I don't know? Is there some other key information being exchanged that my MBP needs to send that I can't see? TIA!! Hello,
I am trying to run a report as per the below mssql query:
$query = "SELECT tblWJCItem.AddedDescription, tblWJC.WJCPrefix, tblWJC.WJCNo, tblWJCItem.MaterialName, tblStockFamily.StockFamily, tblWJCItem.WeightToSend, tblWJC.DateCreated, tblWJC.WJCStatusID FROM tblWJC INNER JOIN tblCustomer ON tblWJC.CustomerID = tblCustomer.CustomerID INNER JOIN tblWJCStockStatus ON tblWJC.WJCStockStatusID = tblWJCStockStatus.WJCStockStatusID INNER JOIN tblStockStatus ON tblWJC.WJCID = tblStockStatus.WJCID LEFT OUTER JOIN tblWJCProductLine ON tblWJC.WJCID = tblWJCProductLine.WJCID LEFT OUTER JOIN tblWJCStockItem ON tblWJCProductLine.WJCProductLineID = tblWJCStockItem.tblWJCStockItem INNER JOIN tblStockFamily ON tblWJCItem.ProductFamilyID = tblStockFamily.StockFamilyID WHERE tblCustomer.CustomerName = 'NAME' AND tblWJCStockStatus.WJCStockStatus <> 'Stock Usage Confirmed' ORDER BY tblWJC.WJCID"; $query = "SELECT Table1.*, Table2.A, Table2.B, Table2.C, Table2.D, Table2.E FROM Table1 INNER JOIN Table2 ON Table1.A = Table2.A Issue is that there are multiple matches for some instances, like so: Table1.A value = 1777 (only once since it is a UniqueID) Table2.A value = 1777 (3 records with this ID value) So I will get 3 records returned since Table1.A matches 3 records in Table2.A. I want to merge the 3 records in Table2 into 1 record and then match that with Table1 where the UniqueID is the same. Make sense? Hi, I knew the way to connect to mysql but i don't know how to connect mssql. Is it the same? here's an example for mysql. please teach me the mssql. $con = new mysqli(DB_HOST,DB_USER,DB_PASS,DB_NAME); $sql = "SELECT COUNT(*) FROM numbers"; $result = $con->query($sql); $r = $result->fetch_row(); thanks Hi every one, i am trying to connect with a remote SQL server on window platform. i have hostname, username, password and database name, but when i try t connect using mssql_connect($server,$username,$password) i got this Warning: mssql_connect() [function.mssql-connect]: Unable to connect to server: Thanks Hi All, I'm hitting brick walls while trying to run a stored procedure query with PDO (linking to MSSQL) If I run the query from SSMS (SQL Server Management Studio) I get a result every time (one single row is returned, as expected) - I'm placing the same query into PHP without any dynamic variables etc - its just a straight query... when it runs the query in PDO it fails every time with this error:
PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IMSSP]: The active result for the query contains no fields.' in index.php:195 The code is as follows:
$SQLSTMTA="exec pEmployeeGetData @EmployeeID='1',@Year='2020'";
$STHA = $DBH2->prepare($SQLSTMTA);
//$STH->bindParam(':EMPID', $EmployeeID);
$STHA->execute();
while($result = $STHA->fetchAll()) { //(row 195)
var_dump($result);
}
I've also tried a few variations on the above such as ->Fetch (as opposed to FetchAll) without any luck. Any ideas on how I can get around this? Thanks in Advance!
|
|||||||