• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 90
  • Last Modified:

how to write mysql query as good php

hello all, what is best way to write this code in php when i try and use the tool in sql workbench it does not work for this query in converting it to php

SET @frf = (SELECT driverId FROM drivers WHERE 1 ORDER BY RAND() LIMIT 1);
select  h.*, g.*,  a.*,b.*, c.*, e.* , f.* from
(SELECT  COUNT(*) firstplaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=1 )as a,
(SELECT COUNT(*) secondPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=2) as b,
(SELECT COUNT(*) thridPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=3) as c,
(SELECT COUNT(*) Poles FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.grid=1) as e,
(select count(r.statusId) Failures FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId > 1) as f,
(select count(r.statusId) Finished FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId = 1) as g,
(select d.surname,count(*) Races FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId ) as h;

Open in new window

0
James Murrell
Asked:
James Murrell
  • 2
  • 2
1 Solution
 
NerdsOfTechTechnology ScientistCommented:
Connect and run compound query:
<?php
// Connect to MSSQL
$link = mssql_connect('SERVER', 'USERNAME', 'PASSWORD');

if (!$link || !mssql_select_db('DATABASE', $link)) {
    die('Unable to connect to database!');
}

// Do a simple query, select the version of 
// MSSQL and print it.
$version = mssql_query(
'
 SET @frf = (SELECT driverId FROM drivers WHERE 1 ORDER BY RAND() LIMIT 1);
 select  h.*, g.*,  a.*,b.*, c.*, e.* , f.* from
 (SELECT  COUNT(*) firstplaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=1) as a,
 (SELECT COUNT(*) secondPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=2) as b,
 (SELECT COUNT(*) thridPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=3) as c,
 (SELECT COUNT(*) Poles FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.grid=1) as e,
 (select count(r.statusId) Failures FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId > 1) as f,
 (select count(r.statusId) Finished FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId = 1) as g,
 (select d.surname,count(*) Races FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId ) as h;
'
);
$row = mssql_fetch_array($version);

while ($row = mssql_fetch_assoc($query)) {
	foreach ($row as $key => $value){
		echo "$key : $value <br>";
	}
	echo "<br><hr><br>";
}

// Clean up
mssql_free_result($version);
?>

Open in new window

0
 
Julian HansenCommented:
@NoT - the query came from SQL Workbench which implies MySQL - but your code is mssql.
1
 
NerdsOfTechTechnology ScientistCommented:
Corrected for MySQL

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = '
 SET @frf = (SELECT driverId FROM drivers WHERE 1 ORDER BY RAND() LIMIT 1);
 select  h.*, g.*,  a.*,b.*, c.*, e.* , f.* from
 (SELECT  COUNT(*) firstplaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=1) as a,
 (SELECT COUNT(*) secondPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=2) as b,
 (SELECT COUNT(*) thridPlaceFinishes FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.position=3) as c,
 (SELECT COUNT(*) Poles FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId AND r.grid=1) as e,
 (select count(r.statusId) Failures FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId > 1) as f,
 (select count(r.statusId) Finished FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId and r.statusId = 1) as g,
 (select d.surname,count(*) Races FROM results r JOIN drivers d WHERE d.driverId=@frf AND r.driverId=d.driverId ) as h;
';

$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        foreach($row as $key => $value){
            echo "$key : $value";
        }
    }
} else {
    echo "no results";
}
$conn->close();
?>

Open in new window

0
 
James MurrellProduct SpecialistAuthor Commented:
cheers it sort of works:     i keep getting no results. which is strange......
http://f1cs97jjm3.ddns.net/random.php

in workbench i get results....
0
 
James MurrellProduct SpecialistAuthor Commented:
cheers
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now