PHP - Mysql To Mysqli Problems :(

On the mainpage I am getting this error:   Fatal error: Call to a member function query() on a non-object in C:\xampp\htdocs\page\controller\function.php on line 393    

	$result = $db->query($query);

And on the admin section:   Notice: Undefined index: role in C:\xampp\htdocs\page\admin\header.php on line 7

Notice: Undefined index: role in C:\xampp\htdocs\page\admin\header.php on line 7

Fatal error: Undefined class constant 'site_url' in C:\xampp\htdocs\page\admin\header.php on line 8    

if ($_SESSION['role'] !== 'admin' and $_SESSION['role'] !== 'moderator'){header( 'Location:'.config::site_url.'index.php' );

Hello (I think I could have posted this in the wrong PHP area! - so posting it here),   I'm pretty new at building websites using php (and mysql) and was most recently given the task to create a database image gallery, which was to be accessed through a php website.  I made a full site which allowed me to upload said images  & it worked perfectly. However after doing my last checks I have been told that mysql is deprecated and that I need to use mysqli.   I've had a look at some tutorials on websites to help direct me but it's simply confusing me more and more each time I look at it.  Is it possible I am over thinking this and there is an easier way to approach it?   Thank you kindly. This is my php code:    

I'm a beginner here and i am learning the basic in converting from MySQL to MySQLi. I am currently working on this registration page which I would want to convert to MySQLi. Please advise me how to modify this script, I would prefer the procedural style. The MySQLi coding is not working because it would notg insert into the database like the MySQL coding would, would appreciate if your can help me. MYSQL

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysql_real_escape_string($_POST['name']);
$name2 = mysql_real_escape_string($_POST['name2']);
$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string($_POST['password']);
$password2 = mysql_real_escape_string($_POST['password2']);
$email2 = mysql_real_escape_string($_POST['email2']);
$address = mysql_real_escape_string($_POST['address']);
$address2 = mysql_real_escape_string($_POST['address2']);
$address3 = mysql_real_escape_string($_POST['address3']);
$address4 = mysql_real_escape_string($_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysql_connect("localhost", "root", "Passw0rd") or die("Connection Error");
    //select database
    mysql_select_db("logindb") or die("Selection Error");
    //namecheck
    $namecheck = mysql_query("SELECT * FROM users WHERE email='{$email}'");
    $count = mysql_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysql_query("INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysql_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

MYSQLi (NOT WORKING AFTER CONVERTING)

<?php
error_reporting(1);
$submit = $_POST['submit'];
//form data
$name = mysqli_real_escape_string($connect, $_POST['name']);
$name2 = mysqli_real_escape_string($connect, $_POST['name2']);
$email = mysqli_real_escape_string($connect, $_POST['email']);
$password = mysqli_real_escape_string($connect, $_POST['password']);
$password2 = mysqli_real_escape_string($connect, $_POST['password2']);
$email2 = mysqli_real_escape_string($connect, $_POST['email2']);
$address = mysqli_real_escape_string($connect, $_POST['address']);
$address2 = mysqli_real_escape_string($connect, $_POST['address2']);
$address3 = mysqli_real_escape_string($connect, $_POST['address3']);
$address4 = mysqli_real_escape_string($connect, $_POST['address4']);
$error = array();
if ($submit) {
    //open database
    $connect = mysqli_connect("localhost", "root", "Passw0rd", "logindb") or die("Connection Error");
    //namecheck
    $namecheck = mysqli_query($connect, "SELECT * FROM users WHERE email='{$email}'");
    $count = mysqli_num_rows($namecheck);
    if($count==0) {
    }
    else
    {
        if($count==1) {
            $error[] = "<p><b>User ID taken. Try another?</b></p>";
        }
    }
    //check for existance
    if($name&&$name2&&$email&&$password&&$password2&&$email2&&$address&&$address2&&$address3&&$address4) {
        if(strlen($password)<8) {
            $error[] = "<p><b>Password must be least 8 characters</b></p>";
        }
        if(!preg_match("#[A-Z]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 upper case characters</b></p>";
        }
        if(!preg_match("#[0-9]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 number</b></p>";
        }
        if(!preg_match("#[\W]+#",$password)) {
            $error[] = "<p><b>Password must have at least 1 symbol</b></p>";
        }
        //encrypt password
        $password = sha1($password);
        $password2 = sha1($password2);
        if($_POST['password'] != $_POST['password2']) {
            $error[] = "<p><b>Password does not match</b></p>";
        }
        //rescue email match check
        if($_POST['email2'] == $_POST['email']) {
            $error[] = "<p><b>Rescue Email must not be the same as User ID</b></p>";
        }
        //generate random code
        $random = rand(11111111,99999999);
        //check for error messages
        if(isset($error)&&!empty($error)) {
            implode($error);
        }
        else
        {
            //Registering to database
            $queryreg = mysqli_query($connect, "INSERT INTO users VALUES ('','$name','$name2','$email','$password','$password2','$email2','$address','$address2','$address3','$address4','$random','0')");
            $lastid = mysqli_insert_id();
            echo "<meta http-equiv='refresh' content='0; url=Activate.php?id=$lastid&code=$random'>";
            die ();
        }
    }
}
?>

Hi, I am trying to convert the register & login script from mysql to mysqli. I have converted the easy parts and have the connection to the database, but the following functions all need changing and I can't work out the correct solution mainly due to the deprecation of mysql_result()

The code that needs updating is
 

<?php
function user_count() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
	return mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
	$file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
	move_uploaded_file($file_temp, $file_path);
	mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
	$user_id 	= (int)$user_id;
	$type 		= (int)$type;
	
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
	$email 		= mysql_real_escape_string($email);
	$email_code = mysql_real_escape_string($email_code);
	
	if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
		mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
		return true;
	} else {
		return false;
	}
}

function user_exists($username) {
	$username = sanitize($username);
	$query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
	return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
	$email = sanitize($email);
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
	$username = sanitize($username);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
	$email = sanitize($email);
	return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
	$user_id = user_id_from_username($username);
	
	mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

	$username = sanitize($username);
	$password = md5($password);
	
	return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) == 1) ? $user_id : false;
}

?>

And here is what the converter gave me:

function user_count() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `active` = 1"), 0);
}

function users_online() {
return mysql_result(mysqli_query($GLOBALS["___mysqli_ston"], "SELECT COUNT(`user_id`) FROM `users` WHERE `logged_in` = 1"), 0);
}

function change_profile_image($user_id, $file_temp, $file_extn) {
    $file_path = 'images/profile/' . substr(md5(time()), 0, 10) . '.' . $file_extn;
    move_uploaded_file($file_temp, $file_path);
    mysql_query("UPDATE `users` SET `profile` = '" . mysql_real_escape_string($file_path) . "' WHERE `user_id` = " . (int)$user_id);
}

function has_access($user_id, $type) {
    $user_id     = (int)$user_id;
    $type         = (int)$type;
    
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `user_id` = $user_id AND `type` = $type"), 0) == 1) ? true : false;
}

function activate($email, $email_code) {
    $email         = mysql_real_escape_string($email);
    $email_code = mysql_real_escape_string($email_code);
    
    if (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email' AND `email_code` = '$email_code' AND `active` = 0"), 0) == 1) {
        mysql_query("UPDATE `users` SET `active` = 1 WHERE `email` = '$email'");
        return true;
    } else {
        return false;
    }
}

function user_exists($username) {
    $username = sanitize($username);
    $query = mysql_query("SELECT COUNT('user_id') FROM `users` WHERE `username` = '$username'");
    return (mysql_result($query, 0) == 1) ? true : false;
}

function email_exists($email) {
    $email = sanitize($email);
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `email` = '$email'"), 0) == 1) ? true : false;
}

function user_id_from_username($username) {
    $username = sanitize($username);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `username` = '$username'"), 0, 'user_id');
}

function user_id_from_email($email) {
    $email = sanitize($email);
    return mysql_result(mysql_query("SELECT `user_id` FROM `users` WHERE `email` = '$email'"), 0, 'user_id');
}

function login($username, $password) {
    $user_id = user_id_from_username($username);
    
    mysql_query("UPDATE `users` SET `logged_in` = 1 WHERE `user_id` = $user_id");

    $username = sanitize($username);
    $password = md5($password);
    
    return (mysql_result(mysql_query("SELECT COUNT(`user_id`) FROM `users` WHERE `username` = '$username' AND `password` = '$password'"), 0) == 1) ? $user_id : false;
}

?>

Please could someone point me in the right direction here?     Also my site works perfectly well with MySQL, do I have to convert it to MySQLi?   Many Thanks   Paul If anyone knows how to solve this, it would be much appreciated. I already have a website template and would prefer to continue with mysqli instead of PDO.

Many Thanks

Paul

I have a prepared statement class for MYSQL, since in PHP 5 this is now changing to mysqli; I'm looking for some help in changing the code from my existing class to the new mysqli.   I have done some research online about changing from mysql to mysqli but the changes I made seems to only cause issues with connecting to the database.   After many hours of changing the existing file using the research online, I've decided to start again and ask others if they would be ever so kind to help this noob out and point out which parts of the script needs to be changed.    Thank you for reading.   

<?php
class Database {
 
    private $host;
    private $user;
    private $pass;
    private $name;
    private $link;
    private $error;
    private $errno;
    private $query;
 
    function __construct($host, $user, $pass, $name = "", $conn = 1) {
        $this -> host = $host;
        $this -> user = $user;
        $this -> pass = $pass;
        if (!empty($name)) $this -> name = $name;      
        if ($conn == 1) $this -> connect();
    }
 
    function __destruct() {
        @mysql_close($this->link);
    }
 
    public function connect() {
        if ($this -> link = mysql_connect($this -> host, $this -> user, $this -> pass, TRUE)) {
            if (!empty($this -> name)) {
                if (!mysql_select_db($this -> name, $this->link)) $this -> exception("Could not connect to the database!");
            }
        } else {
            $this -> exception("Could not create database connection!");
        }
    }
 
    public function close() {
        @mysql_close($this->link);
    }
 
    public function query($sql) {
        if ($this->query = @mysql_query($sql, $this->link)) {
            return $this->query;
        } else {
            $this->exception("Could not query database!");
            return false;
        }
    }
 
    public function num_rows($qid) {
        if (empty($qid)) {         
            $this->exception("Could not get number of rows because no query id was supplied!");
            return false;
        } else {
            return mysql_num_rows($qid);
        }
    }
 
    public function fetch_array($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid);
        }
        return $data;
    }
 
    public function fetch_array_assoc($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch array assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_array($qid, MYSQL_ASSOC);
        }
        return $data;
    }
 
public function fetch_object($qid) {
        if (empty($qid)) {
            $this->exception("Could not fetch object assoc because no query id was supplied!");
            return false;
        } else {
            $data = mysql_fetch_object($qid);
        }
        return $data;
    }
 
    public function fetch_all_array($sql, $assoc = true) {
        $data = array();
        if ($qid = $this->query($sql)) {
            if ($assoc) {
                while ($row = $this->fetch_array_assoc($qid)) {
                    $data[] = $row;
                }
            } else {
                while ($row = $this->fetch_array($qid)) {
                    $data[] = $row;
                }
            }
        } else {
            return false;
        }
        return $data;
    }
 
    public function last_id() {
        if ($id = mysql_insert_id()) {
            return $id;
        } else {
            return false;
        }
    }
 
    private function exception($message) {
        if ($this->link) {
            $this->error = mysql_error($this->link);
            $this->errno = mysql_errno($this->link);
        } else {
            $this->error = mysql_error();
            $this->errno = mysql_errno();
        }
        if (PHP_SAPI !== 'cli') {
        ?>
 
            <div class="alert-bad">
                <div>
                    Database Error
                </div>
                <div>
                    Message: <?php echo $message; ?>
                </div>
                <?php if (strlen($this->error) > 0): ?>
                    <div>
                        <?php echo $this->error; ?>
                    </div>
                <?php endif; ?>
                <div>
                    Script: <?php echo @$_SERVER['REQUEST_URI']; ?>
                </div>
                <?php if (strlen(@$_SERVER['HTTP_REFERER']) > 0): ?>
                    <div>
                        <?php echo @$_SERVER['HTTP_REFERER']; ?>
                    </div>
                <?php endif; ?>
            </div>
        <?php
        } else {
                    echo "MYSQL ERROR: " . ((isset($this->error) && !empty($this->error)) ? $this->error:'') . "\n";
        };
    }
 
}
?>

how i can make a insert using this fuctions I m learning php, as using this functions (mysqli abstract) but after update wont work any more.

/** insert data array */
public function insert(array $arr)
{

if ($arr)
{
$q = $this->make_insert_query($arr);
$return = $this->modifying_query($q);
$this->autoreset();
return $return;
}
else
{
$this->autoreset();
return false;
}
}

complement

/** insert query constructor */
protected function make_insert_query($data)
{
$this->get_table_info();
$this->set_field_types();
if (!is_array(reset($data)))
{
$data = array($data);
}
$keys = array();
$values = array();
$keys_set = false;
foreach ($data as $data_key => $data_item)
{
$values[$data_key] = array();
$fdata = $this->parse_field_names($data);
foreach ($fdata as $key => $val)
{
if (!$keys_set)
{
if (isset($this->field_type[$key]))
{
$keys[] = '`' . $val['table'] . '`.`' . $val['field'] . '`';
}
else
{
$keys[] = '`' . $val['field'] . '`';
}
}
$values[$data_key][] = $this->escape($val['value'], $this->is_noquotes($key), $this->field_type($key), $this->is_null($key),
$this->is_bit($key));
}
$keys_set = true;
$values[$data_key] = '(' . implode(',', $values[$data_key]) . ')';
}
$ignore = $this->ignore ? ' IGNORE' : '';
$delayed = $this->delayed ? ' DELAYED' : '';
$query = 'INSERT' . $ignore . $delayed . ' INTO `' . $this->table . '` (' . implode(',', $keys) . ') VALUES ' . implode(',',
$values);
return $query;
}

before update this class i used to insert data like this

$db = Sdba::table('users');
$data = array('name'=>'adam');
$db->insert($data);

this method of insert dont works on new class. if i try like this i got empty columns and empty values. thanks for any help complete class download http://goo.gl/GK3s4E

Hi there,   I'm new to all this so I've been following instructions from various sites and books.  So far I have installed Apache, PHP and mysql, however when I browse to localhost/test.php (created to view phpinfo() through the browser) i can't see mysql or mysqli only the mysqlnd.   Here is an overview of software I've installed, OS and things I've added so far;   OS ver Windows7 Professional 32-bit VM

Hi,

I'm not sure if this should be posted in the PHP forum or the mySQL forum as it's a bit of both to be honest.

Basically I am generating what I think is a fairly complex SQL statement (although I'm by no means a mySQL master so it might not be that complex) through the use of PHP.

The PHP that is generating the statement looks like so:

Code: [Select]
// Availability
                $availability = 'SELECT * FROM indivdual_teachers WHERE 1=1';
           
                foreach($_GET as $key=>$value){
                    if(strpos($key, 'availability_') == 0 and $value=='Available'){
                        $day = (int)str_replace('availability_', '', $key);
                        $availability .= ' AND teacher_id IN (SELECT candidate_id FROM availability_calendar WHERE (DAY = '.$day.'
                        AND MONTH = '.$month.' AND YEAR = '.$year.' AND availability = "Available")) ';
                    }
                }

// Staff Yype
                if($_GET['staff_required'] == 'Non Teaching Staff') {
               
                    $search_str[] = "role !=\"\"";
           
                } elseif($_GET['staff_required'] == 'Teaching Staff') {
           
                    $search_str[] = "role=\"\"";
           
                } else {
           
                }

// Lives in...
                if(!empty($_GET['location'])) {
           
                    $location = mysql_real_escape_string($_GET['location']);
                    $search_str[] = "address2 LIKE '%$location%' OR city LIKE '%$location%'";
           
                } elseif(!empty($_GET['region'])) {
           
                    $region = mysql_real_escape_string($_GET['region']);
                    $search_str[] = "region = '$region'";
           
                } else {
               
                    // They don't want to search on lives in... so do nothing
               
                }

// Registered with...
                if(!empty($_GET['LA'])) {
               
                    $local_authority = mysql_real_escape_string($_GET['LA']);
                    $search_str[] = "id IN (SELECT teacher_id FROM teacher_LEAs WHERE LEA = '$local_authority')";
           
                } elseif (!empty($_GET['Agency'])) {
           
                    $agency = mysql_real_escape_string($_GET['Agency']);
                    $search_str[] = "id IN (SELECT teacher_id FROM teacher_agencies WHERE agency = '$agency')";
           
                } else {
           
                    // They don't want to search on registered with... so do nothing
           
                }

$total = $_GET["keywords"];
 
                $keyarr = explode(',',$total);
                // In order to  process this  array  values  here is the code
                 
                foreach($keyarr  as  $key=>$value) {
                   
                   //  becareful to check the value for  empty line
                   $value = trim($value);
                   
                   if (!empty($value)) {
                       
                        $search_str[] = "id IN (SELECT teacher_id FROM test_cv_tags WHERE skills = '$value')";
                   
                   }
               
                }

// Subject/Specalism
                if(!empty($_GET['subject'])) {
                    $subject = mysql_real_escape_string($_GET['subject']);
                    $search_str[] = "id IN (SELECT teacher_id FROM teacher_specialisms WHERE specalism = '$subject')";
                }
           
                if(!empty($_GET['additional_subject'])) {
                    $additional_subject = mysql_real_escape_string($_GET['additional_subject']);
                    $search_str[] = "id IN (SELECT teacher_id FROM teacher_additional_subjects WHERE subject = '$additional_subject')";
                }

// Ranking
                if(!empty($_GET['overall'])) {
                    $overall = mysql_real_escape_string($_GET['overall']);
                    $search_str[] = "avg_overall >= $overall ";
           
                } else {
           
                    if(!empty($_GET['behaviour'])) {
                        $behaviour = mysql_real_escape_string($_GET['behaviour']);
                        $search_str[] = "avg_behaviour_management >= $behaviour ";
                    }
                    if(!empty($_GET['ability'])) {
                        $ability = mysql_real_escape_string($_GET['ability']);
                        $search_str[] = "avg_teaching_ability >= $ability ";
                    }
                    if(!empty($_GET['planning'])) {
                        $planning = mysql_real_escape_string($_GET['planning']);
                        $search_str[] = "avg_preperation_planning >= $planning ";
                    }
                    if(!empty($_GET['professional'])) {
                        $professionalism = mysql_real_escape_string($_GET['professional']);
                        $search_str[] = "avg_professionalism >= $professionalism ";
                    }
           
                }
This is all brought together like so;

Code: [Select]
if(!empty($search_str)){
                   
                    $sql = "$availability AND ".join(" AND ", $search_str)." AND public_profile = 'Yes' ORDER BY avg_overall DESC";                   
                   
                    echo $sql;
               
                } else {
               
                    $sql = "$availability AND public_profile = 'Yes' ORDER BY avg_overall DESC";
                   
                    echo $sql;
               
                }

When I complete my form and echo the completed query it looks something like this;

Code: [Select]
SELECT * FROM indivdual_teachers

WHERE 1=1

AND teacher_id IN

(SELECT candidate_id FROM availability_calendar WHERE (DAY = 19 AND MONTH = 04 AND YEAR = 2012 AND availability = "Available"))

AND role=""

AND region = 'North East'

AND id IN

(SELECT teacher_id FROM teacher_agencies WHERE agency = 'Education World')

AND id IN

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'french')

[color=red]AND[/color] id IN

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'golf')

[color=red]AND[/color] id IN

(SELECT teacher_id FROM test_cv_tags WHERE skills = 'spanish')

AND id IN

(SELECT teacher_id FROM teacher_specialisms WHERE specalism = 'Accounting')

AND id IN

(SELECT teacher_id FROM teacher_additional_subjects WHERE subject = 'Art')

AND avg_behaviour_management >= 5

AND avg_teaching_ability >= 5

AND avg_preperation_planning >= 5

AND avg_professionalism >= 5

AND public_profile = 'Yes'

ORDER BY avg_overall DESC
The problem I have is that the part I have highlighted in red should actually be an OR statement in order for the query to work as intended.

I realise why it is an AND statement, simply because it is using this (highlighted in green). I can't simply change the green AND to an OR because that would cause the rest of my query to misfunction;

Code: [Select]
$sql = "$availability AND ".join(" [color=green]AND[/color] ", $search_str)." AND public_profile = 'Yes' ORDER BY avg_overall DESC";
However I have been unable to resolve the issue.

Would someone be kind enough to offer me some advice/guidance here?

Many thanks.

It seems the code below doesn't work properly. Even if there isn't a record that has the same username or name, it will say there is on in the database, which prevents them from making an account. Why so?

Code: [Select]
  $check1 = $db->prepare("SELECT username,name FROM servers WHERE username = ? OR name = ? LIMIT 1");
  $check1->execute(array($username, $password));
               
                if($check1->rowCount() > 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }

And here is my other problem, the harder one. It seems my execute wont work properly. The data will NEVER insert in the database, and it just says: "You've successfully created your account!"

Here is my code:

Code: [Select]
$insertQuery = $db->prepare("INSERT INTO servers (id, username, password, name, type, description, ip, votes, beta)         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");

$insertQuery->execute(array(null, $username, $password, $name, $server_type, $description, $ip, 0, 1));

if($insertQuery->errorInfo() > 0)
{
echo "Sorry, there was the following error:";
print_r($insertQuery->errorInfo());
}
else
{
                                                       echo "Server has been succesfully created!";
}

[MAY LOOK SLOPPY. CODED IN NOTEPAD++, BEST VIEWED WITH IT.]
Full, whole page code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<div id="artlicles">
 
<?php
 
if(!$_SESSION['user'])
{
 
        $username =     $_POST['username'];
        $password =     $_POST['password'];
        $name =         $_POST['name'];
        $server_type =  $_POST['type'];
        $description =  $_POST['description'];
 
        //This check isn't 100% secure. It allows for registration with no server details
        if(!$username || !$password)
        {
       
        //Ideally HTML and PHP should be as separate as possible. Perhaps consider having a different PHP script that runs the registration
        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = $db->prepare("SELECT username,name FROM servers WHERE username = ? OR name = ? LIMIT 1");
                $check1->execute(array($username, $name));
               
                if($check1->rowCount() > 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        $insertQuery = $db->prepare("INSERT INTO servers (id, username, password, name, type, description, ip, votes, beta) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)");
                        $insertQuery->execute(array(null, $username, $password, $name, $server_type, $description, $ip, 0, 1));

if($insertQuery->errorInfo() > 0)
{
echo "Sorry, there was the following error:";
print_r($insertQuery->errorInfo());
}
else
{
                        echo "Server has been succesfully created!";
}
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

Hello Im just new to coding into php and im getting this error? i cant seems to see database into my php ? what wrong need help

 

CREATE TABLE IF NOT EXISTS `payment_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idnumber` varchar(30) NOT NULL,
  `date` varchar(30) NOT NULL,
  `amount` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Here my php code i cant seems to understand what wrong i dont get any error from mysql or from my php  please i dont know what wrong 

 

<label for="filter">Filter</label> <input type="text" name="filter" value="" id="filter" />
								<table cellpadding="1" cellspacing="1" id="resultTable">
									<thead>
										<tr>
											<th  style="border-left: 1px solid #C1DAD7"> Date </th>
											<th>Amount</th>
										</tr>
									</thead>
									<tbody>
								<?php
										include('../connect.php');
									$id=$_SESSION['SESS_MEMBER_ID'];	
										$result = mysql_query("SELECT * FROM student WHERE id='$id'");
										while($row = mysql_fetch_array($result))
											{
												echo '<tr class="record">';
												echo '<td style="border-left: 1px solid #C1DAD7"><div align="left">'.$row['date'].'</div></td>';
												echo '<td><div align="left">'.$row['amount'].'</div></td>';
												echo '</tr>';
											}
										?> 
									</tbody>
								</table>

I have a db.php and inside it I filled out all the info needed:

<?php
define('DB_HOST', 'example.com');
define('DB_NAME', 'database_name');
define('DB_USERNAME', 'user_name');
define('DB_PASSWORD', '*******');

$odb = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD);
?>

My problem is with the DB_HOST, is that my direct URL to the site? for example, google.com or is it like an IP?  

Hi all,

I have a query that runs just fine from the command line in MySQL, but when I try to run the exact same query using PHP to query the same database I get an error.

The query is:

SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close, lat, lng, ( 3959 * acos( cos( radians('41.757787') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('-88.321416') ) + sin( radians('41.757787') ) * sin( radians( lat ) ) ) ) AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName AND OpHours.Close > '17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30

Like I stated above, when I run this query from the MySQL command line it returns 12 rows, which is what it should.

But when I try executing the same query via PHP I get this error message:

Invalid query: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''17:00:00' HAVING distance < '10' ORDER BY distance LIMIT 0 , 30' at line 1

The PHP code is thus:

$connection=mysql_connect ($server, $username, $password);
if (!$connection)
   {
      echo 'Connection error';
     die("Not connected : " . mysql_error());
   }

$db_selected = mysql_select_db($database, $connection);
if (!$db_selected)
   {
      echo 'Database selection error' . '\n';
      die ("Can\'t use db : " . mysql_error());
   }

 $query = sprintf("SELECT Street, City, ZipCode, providers.bWebpage, OpHours.Open, OpHours.Close,
 lat, lng, ( 3959 * acos( cos( radians('%s') ) * cos( radians( lat ) ) * cos( radians( lng ) - radians('%s') ) + sin( radians('%s') ) * sin( radians( lat ) ) ) )
AS distance FROM Providers, OpHours WHERE providers.bName = OpHours.bName %s HAVING distance < '%s' ORDER BY distance LIMIT 0 , 30",
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($center_lng),
  mysql_real_escape_string($center_lat),
  mysql_real_escape_string($hours),
  mysql_real_escape_string($radius));

$result = mysql_query($query);

if (!$result)
   {
      die("Invalid query: " . mysql_error());
   }

I've also checked the MySQL logs and they don't report any errors.  I'm running this on a Mac using Snow Leopard.  MySQL version is 5.1.37 and the PHP version is 5.2.11.  If it makes any difference both of these are from MAMP version 1.8.4.

Hello PHPFreaks.

I have one problem, that I've tried to fix for a day or two now.
I need a function, that picks out a random row in a table, and returns one thing.

I tried with, getting the max rows in the table, make a random number in the interval 0 to max rows, then select the random number, but it failed.

I hope someone can help me out with this one?

-Niixie

This is my code, and I know it's pretty messy, because i fooled around with it for a day or two, so it's hard to find head or tail in it.
Code: [Select]
$query0 = sprintf("SELECT * FROM registertest");
        $result = mysql_query($query0);
       
        if (!$result) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $query;
            die($message);
        }
       
        $nums = mysql_num_rows($result);
        $rand_nums = rand(0,$nums);
       
        $query1 = sprintf("SELECT question FROM registertest WHERE id='%i'",
                $rand_nums);
        $result1 = mysql_query($query1) or die(mysql_error());
       
        if (!$result1) {
            $message  = 'Invalid query: ' . mysql_error() . "\n";
            $message .= 'Whole query: ' . $query;
            die($message);
        }
       
        $row = mysql_fetch_row($result1);
        mysql_free_result($result);
        mysql_free_result($result1);
        $_SESSION['registerquestion'] = $row;
        if(strlen($_SESSION['registerquestion'])!=0){return 1;}else{return 0;}

I wasn't too sure if this should be in the MySQL help section so please (admin/mods) feel free to move this if you feel it's necessary.

I'm attempting to connect to a URL through cURL that is being grabbed from a mysql table.

The url within the table is:

http://es-us.noticias.yahoo.com/ue-podr%C3%ADa-revisar-veda-crudo-ir%C3%A1n-en-pr%C3%B3ximos-124018543.html"

When I plug this into firefox it works.

When using it through cURL directly (see below) it works.

Code: [Select]

$url = "http://es-us.noticias.yahoo.com/ue-podr%C3%ADa-revisar-veda-crudo-ir%C3%A1n-en-pr%C3%B3ximos-124018543.html";

   // create a new curl resource
   $ch = curl_init();
   
   // set URL to download
   curl_setopt($ch, CURLOPT_URL, $url);


   // user agent:
   curl_setopt($ch, CURLOPT_USERAGENT, "MozillaXYZ/1.0");

   // remove header? 0 = yes, 1 = no
   curl_setopt($ch, CURLOPT_HEADER, 0);

   // should curl return or print the data? true = return, false = print
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
   
// timeout in seconds
   curl_setopt($ch, CURLOPT_TIMEOUT, 60);
   
   // download the given URL, and return output
   $page = curl_exec($ch);


However, when I grab the url from a table within mysql it does not work and no curl error (or any error for that matter) is returned.

Code: [Select]
$get_url_links = "SELECT * FROM tabe";
$url_link = mysql_query($get_url_links);

$row = mysql_fetch_array($url_link);
$url = $row['link']."<br />";

I'm suspecting this is some kind of encoding issue. I've tried

$url = mysql_real_escape_string($url); --- before entering it into the table
$url = mysql_real_escape_string($url); --- before calling cURL
mysql_query("set names 'utf8'"); --- before inserting the url into the table

I've also set the column table type to 'text'.

Suggestions appreciated. 

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=323935.0