PHP - Complicated Php/mssql Query - Help Needed

I have this kind of query:

SELECT db1.field, db2.field FROM table1 db1 INNER JOIN table2 db2 ON db1.matchingField=db2.matchingField INNER JOIN table3 db3 ON db2.matchingField=db3.matchingField

(table 3 is used as there are various other WHERE clauses etc.)

and need to add in so it pulls info from a 4th table

SELECT db1.field FROM table1 db1 UNION ALL SELECT db4.newField FROM table4

How would I merge the two queries?

hi i have the script below which copies data from one table to another but will only insert new data update current data or delete old data from tempproducts to products then it will delete the tempproducts from the db however i keep getting this error:

Warning: mssql_query() [function.mssql-query]: Query failed in E:\UpdateProducts.php on line 33

updateproducts.php
Code: [Select]
<?php
include('../../otherscripts/functions.php');
$log = new Logging(); 

// create DB connection
 $host = "localhost";
 $user = "user";
 $pass = "pass";
 $mydb = "db";

 $db = mssql_connect($host,$user,$pass);
 
//Select Database
 mssql_select_db($mydb);
 

 // delete all old data
    $sql0 = "SELECT * FROM tempproduct";
    $sql1 = "INSERT INTO products SELECT * FROM tempproduct WHERE manf_part_no NOT IN (SELECT manf_part_no FROM products) AND supp_id NOT IN (SELECT supp_id FROM products)";
    $sql2 = "DELETE FROM products WHERE manf_part_no NOT IN (SELECT manf_part_no FROM tempproduct) AND supp_id NOT IN (SELECT supp_id FROM tempproduct)";
    $sql3 = "UPDATE p1 SET p1.avail_qty = t1.avail_qty, p1.cost_price = t1.cost_price, p1.rrp = t1.rrp, p1.date_added = t1.date_added, p1.description = t1.description FROM Products p1 INNER JOIN tempproduct t1 ON (p1.manf_part_no = t1.manf_part_no AND p1.supp_id = t1.supp_id)";
    $sql4 = "TRUNCATE TABLE tempproduct";

//If tempproduct is empty done Execute Commands if it is full then execute commands

$query = mssql_query($sql0) or die($log->lwrite('Failed to select for count from db'));
$rowcount = mssql_num_rows($query);

if($rowcount == 0){
$log->lwrite('Teh tempproduct am emptyish');
}
else{
   mssql_query($sql1) or die($log->lwrite('Failed to insert to db'.$sql1));
   mssql_query($sql2) or die($log->lwrite('Failed to Delete from db'));
   mssql_query($sql3) or die($log->lwrite('Failed to Update db'));
   mssql_query($sql4) or die ($log->lwrite('Failed to TRUNCATE db'));
}
?>

if i run $sql1 command in the sql manager it runs fine and no errors occur?

i have the following code in php:
[PHP]
<?php
error_reporting (E_ALL);

$host="localhost"; // Host name
$username="elemental"; // username
$password="rclogin"; // password
$db_name="traffic2"; // Database name
//$rc_profile_table="rc_profile_table"; // Table name
//$rc_profile_relation_table="rc_profile_relation_table"; // Table name

 
mysql_connect("$host", "$username", "$password");
mysql_select_db("$db_name");
                        
$sql="SELECT created_at FROM rc_profile_table where created_at > 2011-04-19 08:00:00";
$result=mysql_query($sql);
$count=mysql_num_rows($result);

$sql="SELECT created_at FROM rc_profile_relation_table where created_at > 2011-04-19 08:00:00";
$result2=mysql_query($sql);
$count2=mysql_num_rows($result);

 mysql_close();

?>

am i on the right path please?
thank you

I have a database lets say punch_log

The data in the table looks like:
---------------------------------------------------------------------------
|punch_log_id |  user_id | punch_id |   punch_time           |
---------------------------------------------------------------------------
|   10010         |  21         |   1       | 2010-11-10 15:04:59|
|   10011         |  21         |       2       | 2010-11-10 15:50:05|
|   10010         |  21         |   1       | 2010-11-11 15:04:59|
|   10011         |  21         |       2       | 2010-11-11 15:50:05|
|   10010         |  21         |   1       | 2010-11-12 15:04:59|
|   10011         |  21         |       2       | 2010-11-12 15:50:05|
|   10010         |  21         |   1       | 2010-11-13 15:04:59|
|   10011         |  21         |       2       | 2010-11-13 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:04:59|
|   10011         |  21         |       2       | 2010-11-14 15:50:05|
|   10010         |  21         |   1       | 2010-11-14 15:50:59|  <-- this is why i need this.
|   10011         |  21         |       2       | 2010-11-14 15:55:05|  <-- this is why i need this.
----------------------------------------------------------------------------

Im currently using :

$kust  = $_POST['AKuu'];
$kuni3 = $_POST['LKuu'];
$valitudtootaja   = $_POST['TNimi'];


mysql_select_db($database, $con);
$query2 = "SELECT *, SUM(punch_id) FROM punch_log WHERE user_id = '".$valitudtootaja."' AND punch_time   BETWEEN '$kust' AND '$kuni3' AND punch_id ='1' ";   
$result2 = mysql_query($query2) or die(mysql_error());
while($row = mysql_fetch_array($result2)){
   $X = $row['SUM(punch_id)'];

When using the current query im getting

Workers days at work = 6
it should be 5 but thats why i need some solution to group dates and then sum them.

Does anybody have any ideas?

I'm attempting to implement a simple social networking system but at the moment am confused about how to create a multiple query which will display a certain user's friends list.

The database contains four tables, the two tables that I'm using at the moment at 'usersTable' and 'friendshipsTable' are detailed below.


usersTable           | Table that stores all the user data

UserID         | Default primary key
Forename              |
Surname         |
Username           |
Password              |
Email Address      |


friendshipTable   | Table that stores information about friendships between users

FriendshipID      |  Default primary key
userID_1                |  UserID
userID_2         |  UserID
Status         |  Either Pending or Confirmed.

The user's id is parsed into the url, and then saved into a variable.

blah.com/userprofile.php?id=6

$id = $_GET['id']; 

I am familiar with creating simple queries, but can't quite work out how to set up multiple table queries.

What the query needs to do is to check the userID that is parsed with the url, and then check the friendshipsTable by checking if either the userID_1 or userID_2 field  matches the userID to grab the records from the table where there is a match.

The next step is to check to see if the friendship is 'Confirmed' or 'Pending' and if it is 'Pending' to ignore it. Once the records have then been chosen I need the query to then check the value in either userID_1 or userID_2 that doesn't match userID and then pull the user's username and name from the usersTable so it can be displayed on a webpage.

I've no idea hoe much I may or may not be overcomplicating this, an example of the code that I've got so far for this query can be found below, but that's as far as I've got at the moment.

$displayFriends = mysql_query("SELECT * FROM friendshipTable, usersTable WHERE friendshipTable.userID_1='$id' OR friendshipTable.userID_2='$id' ");

Cheers for any help.

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.

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="&#26085;&#24120;&#29983;&#27963;";


$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'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

$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 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,
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