PHP Query

[dominicmcafee]

I have the following code:

<?php

require_once("../db_settings.php");
require_once("../classes/dbHandler.class.php");
require_once("../classes/helpers.class.php");

$db = new DBConnection();
$fetchItems = new dbHandler($db, 'exp_weblog_data');

$return_arr = array();

if (isset($_GET["term"])) {

    $param = htmlspecialchars($_GET["term"]);
    $items = $fetchItems->getResultsByTerm($param, 'field_id_5', 10);

    /* Toss back results as json encoded array. */
    echo json_encode($items);

} else if(isset($_GET['debug'])) {

    $param = 0;
    $items = $fetchItems->getResultsByTerm($param, 'field_id_5', 10);

    $printArry = new helpers();
    $printArry->printArray($items);

}

It basically pulls information from table "exp_weblog_data" and column "field_id_5". I need it however, to pull relevant information from another table called "exp_weblog_titles" and the column "title".

 

If anyone could help me on this matter I would really appreciate it

[gristoi]

what does the getResultsByterm method do? you need to post more code

[dominicmcafee]

ok so here is my dbHandler.class.php:

<?php

/**
 * Class dbHandler handles most database CRUD functions.
 * Simplifying the insert and updating by matching field inputs with the column names in the
 * table you are working with.
 */

require_once('db_connection.class.php');

class dbHandler
{

    var $sqlString;
    var $table;
    var $setTerm;
    var $set;
    var $columns;

    public function __construct(DBConnection $db, $table)
    {
        $this->mysqli = $db->getLink();
        $this->table = $table;
        $this->initColumns($table);
    }

    public function setQuery($sql)
    {
        return $this->sqlString = $sql;
    }


    /**
     * initColumns
     * Gathers and sets the array of columns for the class
     * @param  string $tablename Tablename passed into the call
     * @return void
     */

    /** */

    function initColumns()
    {
        unset($this->columns);

        $result = $this->mysqli->query("DESCRIBE $this->table");
        while ($row = $result->fetch_object()) {
            $this->columns[$row->Field] = $row->Type;
        }
    }

    /**
     * @param $term - Value to search for
     * @param $where - Column to search on
     * @param $limit - Limit the number of returned data
     * @return array
     */

    public function getResultsByTerm($term, $where, $limit)
    {

        $term = $this->mysqli->real_escape_string($term);
        $where = $this->mysqli->real_escape_string($where);

        $query = "SELECT * FROM $this->table WHERE " . $where . " LIKE '%" . $term . "%' LIMIT " . $limit;

        $result = $this->mysqli->query($query) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        $function_result = array();
        $i = 0;
        while ($row = $result->fetch_object()) {
            $function_result[$i] = $row;
            $i++;
        }

        $return_results = $this->stripslashesFull($function_result);

        return $return_results;

    }

    /**
     * @param $where - Syntax ('where column = value')
     * @return array
     */

    public function getResultsWhere($where)
    {

        $where = $this->mysqli->real_escape_string($where);

        $query = "SELECT * FROM $this->table " . $where . " ";

        $result = $this->mysqli->query($query) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        $function_result = array();
        $i = 0;

        while ($row = $result->fetch_object()) {

            $function_result[$i] = $row;
            $i++;

        }

        $return_results = $this->stripslashesFull($function_result);

        return $return_results;

    }

    /**
     * Get all data from table
     * @return array
     */

    public function getAllResults()
    {

        $query = "SELECT * FROM $this->table ";

        $result = $this->mysqli->query($query) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        $function_result = array();
        $i = 0;
        while ($row = $result->fetch_object()) {
            $function_result[$i] = $row;
            $i++;
        }

        $return_results = $this->stripslashesFull($function_result);

        return $return_results;

    }

    /**
     * Get by passed SQL statement and return values
     * @param $sql - Standard SQL pattern
     * @return array
     */
    public function getBySQL($sql)
    {

        $thisSql = $this->mysqli->real_escape_string($sql);

        $result = $this->mysqli->query($thisSql) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        $function_result = array();
        $i = 0;
        while ($row = $result->fetch_object()) {
            $function_result[$i] = $row;
            $i++;
        }

        $return_results = $this->stripslashesFull($function_result);

        return $return_results;

    }

    /**
     * Insert data into table by passed SQL statement
     * @param $sql - Standard SQL pattern
     * @return bool|mysqli_result
     */

    public function insertBySQL($sql)
    {
        $thisSql = $this->mysqli->real_escape_string($sql);
        $result = $this->mysqli->query($thisSql) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        return $result;
    }

    /**
     * @param $post
     * @return mixed
     */
    public function replaceIntoDatabase(array $post)
    {
        $query = "REPLACE INTO " . $this->table;
        $fis = array();
        $vas = array();

        foreach ($post as $field => $val) {
            $fis[] = $field; //you must verify keys of array outside of function;
            $vas[] = "'" . $this->mysqli->real_escape_string($val) . "'";
        }

        $query .= " (" . implode(", ", $fis) . ") VALUES (" . implode(", ", $vas) . ")";
        $result = $this->mysqli->query($query) or die("SQL Error: " . $this->mysqli->error . __LINE__);
        $lastInsertId = $this->mysqli->insert_id;

        return $lastInsertId;

    }

    /**
     * Insert data into table using passed $_POST form data.
     * @param array $post
     * @return mixed
     */
    public function insertIntoDatabase(array $post)
    {
        $query = "INSERT INTO $this->table ";

        $columnkeys = array_keys($this->columns);

        // look for key matches from args to table column and build set
        foreach ($post as $key => $val) {
            if (in_array($key, $columnkeys)) {
                $updateset[$key] = $val;
            }
        }

        foreach ($updateset as $field => $val) {
            $this->set .= $field . " = '" . $this->mysqli->real_escape_string($val) . "',";
        }


        // remove extra trailing comma
        $this->set = 'SET ' . substr($this->set, 0, -1) . ' ';

        $result = $this->mysqli->query($query . $this->set) or die("SQL Error: " . $this->mysqli->error . __LINE__);
        $insertId = $this->mysqli->insert_id;
        return $insertId;
    }


    /**
     * Update $_POST form data where passed where clause
     * @param $post
     * @param $where
     * @return bool|mysqli_result
     */
    public function updateDatabaseWhere($post, $where)
    {

        $thisWhere = $this->mysqli->real_escape_string($where);

        $query = "UPDATE $this->table ";

        $columnkeys = array_keys($this->columns);

        // look for key matches from args to table column and build set
        foreach ($post as $key => $val) {
            if (in_array($key, $columnkeys)) {
                $updateset[$key] = $val;
            }
        }

        foreach ($updateset as $field => $val) {
            $this->set .= $field . " = '" . $this->mysqli->real_escape_string($val) . "',";
        }


        // remove extra trailing comma
        $this->set = 'SET ' . substr($this->set, 0, -1) . ' ';

        $result = $this->mysqli->query($query . " " . $this->set . " " . $thisWhere) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        return $result;
    }

    /**
     * Delete where passed where clause
     * @param $where
     * @return bool|mysqli_result
     */
    public function deleteWhere($where)
    {

        $thisWhere = $this->mysqli->real_escape_string($where);
        $query = "DELETE FROM $this->table";
        $result = $this->mysqli->query($query . " " . $thisWhere) or die("SQL Error: " . $this->mysqli->error . __LINE__);

        return $result;

    }

    /**
     * Clean up data passed to this function.  Can be array or string
     * @param $input
     * @return array|object|string
     */
    private function stripslashesFull($input)
    {
        if (is_array($input)) {
            $input = array_map(array($this, 'stripslashesFull'), $input);
        } elseif (is_object($input)) {
            $vars = get_object_vars($input);
            foreach ($vars as $k => $v) {
                $input->{$k} = $this->stripslashesFull($v);
            }
        } else {
            $input = stripslashes($input);
        }
        return $input;
    }

}

Here is /helpers.class.php:

<?php

class helpers
{

    /**
     * Redirect to the path passed.
     * @param null $location - The location of the
     */
    public function redirect_to($location = NULL)
    {
        if ($location != NULL) {
            header("Location: {$location}");
            exit;
        }
    }

    /**
     * Set message in session
     * @param $type
     * @param $message
     */
    public function setAlert($type, $message)
    {
        $_SESSION['alertMessage'] = '<div class="alertMessage"><span><div class="alert ' . $type . '">' . $message . '</div></span></div>';
    }

    /**
     * Print alert in session
     * @return mixed
     */
    public function printAlert()
    {
        return $_SESSION['alertMessage'];
    }

    /**
     * Unset alert in session
     */
    public function unsetAlert()
    {
        unset($_SESSION['alertMessage']);
    }

    /**
     * Clean way to view array data
     * @param array $arr
     */
    public function printArray($arr)
    {
        echo '<pre>', print_r($arr, 1), '</pre>';
    }

    public function validateInput($postValues, $os)
    {
        foreach ($postValues as $k => $v) {
            if (in_array($k, $os)) {
                foreach ($v as $x) {
                    if ($x == null) {
                        return true;
                    } else {
                        return false;
                    }
                }
            }
        }
    }


}

[gristoi]

$db = new DBConnection();
$fetchItems = new dbHandler($db, 'exp_weblog_titles');
$param = htmlspecialchars($_GET["term"]);
$items = $fetchItems->getResultsByTerm($param, 'title', 10);

term = the where in the query, 'title' if the column you want, table is set in the construct of the handler

Edited March 27, 2014 by gristoi

[dominicmcafee]

Sorry I should have mentioned that this is for an autocomplete invoice system and I need to pull the data from the other table "exp_weblog_titles" that links to this table 'exp_weblog_data' based on the the id - "entry_id".  This was my initial query code which works fine:

$query = "SELECT field_id_5, exp_weblog_titles.title, field_id_57  
          FROM exp_weblog_data, exp_weblog_titles 
          WHERE exp_weblog_titles.entry_id = exp_weblog_data.entry_id AND            field_id_5
          LIKE '%". $param ."%'
          LIMIT 10";

But an update was done on the system and the query was all changed.  Im pretty bad at php.  Sorry for the confusion but I would be extremely greatful if anyone can help me.

[dominicmcafee]

Any takers?

[gristoi]

$query = "SELECT exp_weblog_data.field_id_5, exp_weblog_titles.title, field_id_57

FROM exp_weblog_data inner join  exp_weblog_titles ON exp_weblog_data.id = exp_weblog_titles.entry_id

WHERE  exp_weblog_data.field_id_5

LIKE '%". $param ."%'

LIMIT 10";

[dominicmcafee]

This helps alot guys! Many Thanks!!!!!!!!!!!!!!!!!!!!!