php error ( Call to a member function query() on resource )

Question

<?php
$dbhost='localhost:3306';
$dbuser='root';
$dbpass='';
$con=mysql_connect($dbhost,$dbuser,$dbpass);
if(!$con) {
die("Couldnotconnect:".mysql_error());
}
mysql_select_db('shop');
$sql="SELECTCD_ID,Title,Artist,Year,Company,Price,Quantity,TypeFROMcd_data";
$result=$con->query($sql); //somethingiswronghere
$numRows=$result->num_rows; //somethingiswronghere
if($result->num_rows) { //somethingiswronghere
//outputdataofeachrow

//somethingiswronghere
while($row=$result->fetch_assoc($sql)) {
  echo"id:".$row["CD_ID"]."Name:".$row["Title"]."".$row["Artist"].$row["Year"].$row["Company"].$row["Price"]."Euro".$row["Quantity"].$row["Type"];
}
}
else {
  echo"0results";
}
mysql_close($con);
?>

Answer 1

There are so many issues in your code:

1. You want to use mysqli_* but using mysql_* functions. You can't mix both.
2. Your query is not looking good.

SELECTCD_ID,Title,Artist,Year,Company,Price,Quantity,TypeFROMcd_data

I think your query should be like this:

SELECT CD_ID,Title,Artist,Year,Company,Price,Quantity,Type FROM cd_data

Here is the complete example of your code by using MYSQLi Object Oriented:

$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 = "SELECT CD_ID,Title,Artist,Year,Company,Price,Quantity,Type FROM cd_data";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        //your stuff
    }    
} 
else 
{
    echo "0 results";
}
$conn->close();