phpmyadmin database query and displaying

[Cathryn]

Please assist, i am new to php and i am trying to retrieve information from my database but i am failing to display and there is something wrong with my where query.

 

Here is my code

 

<?php

// Make a MySQL Connection

mysql_connect("localhost", "root", "") or die(mysql_error());

echo "Connected to MySQL<br />";

 

// Make a MySQL db Connection

mysql_select_db("dreamhome") or die(mysql_error());

echo "Connected to Database";

 

// Construct our join query

$query = "SELECT branch.city, property.city ".

"FROM branch, property ".

"WHERE branch.city != NULL && property.city = NULL";

 

 

$result = mysql_query($query) or die(mysql_error());

 

echo $row['city'];

 

?>

[teynon]

You haven't created $row.

 

Add this after your mysql_query statement:

 

while ($row = mysql_fetch_array($result)) {
print_r($row);
}

[Cathryn]

That works but what is whong with my phpmyadmin query?

[darkfreaks]

why not use a mysql class to connect and query everything

 

mysql.class.php

// MySQL Class
class MySQL {
// Base variables
var $sLastError; // Holds the last error
var $sLastQuery; // Holds the last query
var $aResult; // Holds the MySQL query result
var $iRecords; // Holds the total number of records returned
var $iAffected; // Holds the total number of records affected
var $aRawResults; // Holds raw 'arrayed' results
var $aArrayedResult; // Holds a single 'arrayed' result
var $aArrayedResults; // Holds multiple 'arrayed' results (usually with a set key)

var $sHostname = MYSQL_HOST; // MySQL Hostname
var $sUsername = MYSQL_USER; // MySQL Username
var $sPassword = MYSQL_PASS; // MySQL Password
var $sDatabase = MYSQL_NAME; // MySQL Database

var $sDBLink; // Database Connection Link

// Class Constructor
// Assigning values to variables
function MySQL(){
$this->Connect();
}

// Connects class to database
// $bPersistant (boolean) - Use persistant connection?
function Connect($bPersistant = false){
if($this->sDBLink){
mysql_close($this->sDBLink);
}

if($bPersistant){
$this->sDBLink = mysql_pconnect($this->sHostname, $this->sUsername, $this->sPassword);
}else{
$this->sDBLink = mysql_connect($this->sHostname, $this->sUsername, $this->sPassword);
}

if (!$this->sDBLink){
    $this->sLastError = 'Could not connect to server: ' . mysql_error();
return false;
}

if(!$this->UseDB()){
$this->sLastError = 'Could not connect to database: ' . mysql_error();
return false;
}
return true;
}

// Select database to use
function UseDB(){
if (!mysql_select_db($this->sDatabase)) {
$this->sLastError ='Cannot select database: ' . mysql_error();
return false;
}else{
return true;
}
}

// Executes MySQL query
function ExecuteSQL($sSQLQuery){
$this->sLastQuery = $sSQLQuery;
if($this->aResult = mysql_query($sSQLQuery)){
$this->iRecords = @mysql_num_rows($this->aResult);
$this->iAffected = @mysql_affected_rows();
return true;
}else{
$this->sLastError = mysql_error();
return false;
}
}

// Adds a record to the database
// based on the array key names
function Insert($aVars, $sTable, $aExclude = ''){
// Catch Exceptions
if($aExclude == ''){
$aExclude = array();
}

array_push($aExclude, 'MAX_FILE_SIZE');

// Prepare Variables
$aVars = $this->SecureData($aVars);

$sSQLQuery = 'INSERT INTO `' . $sTable . '` SET ';
foreach($aVars as $iKey=>$sValue){
if(in_array($iKey, $aExclude)){
continue;
}
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '", ';
}

$sSQLQuery = substr($sSQLQuery, 0, -2);

if($this->ExecuteSQL($sSQLQuery)){
return true;
}else{
return false;
}
}

// Deletes a record from the database
function Delete($sTable, $aWhere='', $sLimit='', $bLike=false){
$sSQLQuery = 'DELETE FROM `' . $sTable . '` WHERE ';
if(is_array($aWhere) && $aWhere != ''){
// Prepare Variables
$aWhere = $this->SecureData($aWhere);

foreach($aWhere as $iKey=>$sValue){
if($bLike){
$sSQLQuery .= '`' . $iKey . '` LIKE "%' . $sValue . '%" AND ';
}else{
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" AND ';
}
}

$sSQLQuery = substr($sSQLQuery, 0, -5);
}

if($sLimit != ''){
$sSQLQuery .= ' LIMIT ' .$sLimit;
}

if($this->ExecuteSQL($sSQLQuery)){
return true;
}else{
return false;
}
}

// Gets a single row from $1
// where $2 is true
function Select($sFrom, $aWhere='', $sOrderBy='', $sLimit='', $bLike=false, $sOperand='AND'){
// Catch Exceptions
if(trim($sFrom) == ''){
return false;
}

$sSQLQuery = 'SELECT * FROM `' . $sFrom . '` WHERE ';

if(is_array($aWhere) && $aWhere != ''){
// Prepare Variables
$aWhere = $this->SecureData($aWhere);

foreach($aWhere as $iKey=>$sValue){
if($bLike){
$sSQLQuery .= '`' . $iKey . '` LIKE "%' . $sValue . '%" ' . $sOperand . ' ';
}else{
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" ' . $sOperand . ' ';
}
}

$sSQLQuery = substr($sSQLQuery, 0, -5);

}else{
$sSQLQuery = substr($sSQLQuery, 0, -7);
}

if($sOrderBy != ''){
$sSQLQuery .= ' ORDER BY ' .$sOrderBy;
}

if($sLimit != ''){
$sSQLQuery .= ' LIMIT ' .$sLimit;
}

if($this->ExecuteSQL($sSQLQuery)){
if($this->iRecords > 0){
$this->ArrayResults();
}
return true;
}else{
return false;
}

}

// Updates a record in the database
// based on WHERE
function Update($sTable, $aSet, $aWhere, $aExclude = ''){
// Catch Exceptions
if(trim($sTable) == '' || !is_array($aSet) || !is_array($aWhere)){
return false;
}
if($aExclude == ''){
$aExclude = array();
}

array_push($aExclude, 'MAX_FILE_SIZE');

$aSet = $this->SecureData($aSet);
$aWhere = $this->SecureData($aWhere);

// SET

$sSQLQuery = 'UPDATE `' . $sTable . '` SET ';

foreach($aSet as $iKey=>$sValue){
if(in_array($iKey, $aExclude)){
continue;
}
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '", ';
}

$sSQLQuery = substr($sSQLQuery, 0, -2);

// WHERE

$sSQLQuery .= ' WHERE ';

foreach($aWhere as $iKey=>$sValue){
$sSQLQuery .= '`' . $iKey . '` = "' . $sValue . '" AND ';
}

$sSQLQuery = substr($sSQLQuery, 0, -5);

if($this->ExecuteSQL($sSQLQuery)){
return true;
}else{
return false;
}
}

// 'Arrays' a single result
function ArrayResult(){
$this->aArrayedResult = mysql_fetch_assoc($this->aResult) or die (mysql_error());
return $this->aArrayedResult;
}

// 'Arrays' multiple result
function ArrayResults(){
$this->aArrayedResults = array();
for ($i = 0; $aData = mysql_fetch_assoc($this->aResult); $i++){
$this->aArrayedResults[] = $aData;
}
return $this->aArrayedResults;
}

// 'Arrays' multiple results with a key
function ArrayResultsWithKey($sKey='id'){
if(isset($this->aArrayedResults)){
unset($this->aArrayedResults);
}
$this->aArrayedResults = array();
while($aRow = mysql_fetch_assoc($this->aResult)){
foreach($aRow as $sTheKey => $sTheValue){
$this->aArrayedResults[$aRow[$sKey]][$sTheKey] = $sTheValue;
}
}
return $this->aArrayedResults;
}

// Performs a 'mysql_real_escape_string' on the entire array/string
function SecureData($aData){
if(is_array($aData)){
foreach($aData as $iKey=>$sVal){
if(!is_array($aData[$iKey])){
$aData[$iKey] = mysql_real_escape_string($aData[$iKey]);
}
}
}else{
$aData = mysql_real_escape_string($aData);
}
return $aData;
}
}
?>

 

 

example usage

    include_once('/path/to/class.MySQL.php');  
    $oMySQL = new MySQL();  
    $oMySQL->ExecuteSQL($query);  

//array query results
   $oMySQL->ArrayResults();  

 

https://github.com/a1phanumeric/PHP-MySQL-Class/blob/master/readme.textile

 

that explains how to setup a config file for your database connection.

[Cathryn]

Thank you, the error is solved now