MySQLi and PHP: Combine a SELECT query with a WHERE clause

Question

This is the PHP code to select all data from a table.

<?php

/* array for JSON response */
$response = array();

/* CONNECTION SETTINGS */
$DB_HOST = '192.168.0.200';
$DB_UNAME = 'college';
$DB_PWD = 'TEST1234';
$DB_DATABASE = 'college_mgmt';

/* Connecting to mysql database */
$mysqli = new mysqli($DB_HOST, $DB_UNAME, $DB_PWD, $DB_DATABASE);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

/* CONSTRUCT THE QUERY */
$query = "SELECT * FROM book_mst";
$result = $mysqli->query($query) or die($mysqli->error.__LINE__);

if ($result === false)  {
    trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->error, E_USER_ERROR);
} else  {

    $response["book_mst"] = array();

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

        $book = array();

        /* ADD THE TABLE COLUMNS TO THE JSON OBJECT CONTENTS */
        $book["BookID"] = $row['BookId'];


        $book["BookName"] = $row['BookName'];

    $book["Author"] = $row['Author']; 
    $book["Publication"] = $row['Publication']; 
    $book["BookBarCode"] = $row['BookBarCode']; 



        array_push($response["book_mst"], $book);

        // $response[] = $row;
    }
    // success
    $response["success"] = 1;

    echo(json_encode($response));
}

/* CLOSE THE CONNECTION */
mysqli_close($mysqli);
?>

And this is the PHP code to insert a row in a table .

<?php

// array for JSON response
$response = array();

/* CONNECTION SETTINGS */
$DB_HOST = '192.168.0.200';
$DB_UNAME = 'college';
$DB_PWD = 'TEST1234';
$DB_DATABASE = 'college_mgmt';

/* Connecting to mysql database */
$mysqli = new mysqli($DB_HOST, $DB_UNAME, $DB_PWD, $DB_DATABASE);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

// check for required fields
if (isset($_POST['BookName'])&&isset($_POST['Author'])&&isset($_POST['Publication'])&&isset($_POST['BookBarCode'])) {

    $BookName = $_POST['BookName'];
    $Author = $_POST['Author'];
    $Publication = $_POST['Publication'];
    $BookBarCode = $_POST['BookBarCode'];



    // mysql inserting a new row
    $query = "INSERT INTO book_mst(BookName,Author,Publication,BookBarCode) VALUES(?,?,?,?)";
    $statement = $mysqli->prepare($query);

    //bind parameters for markers, where (s = string, i = integer, d = double,  b = blob)
    $statement->bind_param('ssss', $BookName, $Author,$Publication,$BookBarCode);

    if($statement->execute())   {
        print 'Success! ID of last inserted record is : ' .$statement->insert_id .'<br />'; 
    } else {
        die('Error : ('. $mysqli->errno .') '. $mysqli->error);
    }

    $statement->close();
} else {
    // required field is missing
    $response["missing"] = 0;
    $response["message"] = "Required field(s) is missing";

    // echoing JSON response
    echo json_encode($response);
}

/* CLOSE THE CONNECTION */
mysqli_close($mysqli);
?>

I am stuck on combining the two to pass value to the PHP and forming a statement like this:

SELECT * FROM book_mst WHERE BookBarCode ='0123456789';

I appreciate any help on this.

because the data has been pass from the android application. — Tanmay Divekar, Apr 05 '15 at 11:36
@TanmayDivekar: Welcome to StackOverflow. See if this answer here helps: http://stackoverflow.com/a/966717/450534. It does not concern Android. But the PHP could be useful. — Siddharth Lele, Apr 05 '15 at 12:51

score 0 · Answer 1 · edited Apr 05 '15 at 18:47

0

Your query is not working because you aren't using the right column name. In your insert query, you're using BookBarCode, if that is working, then your select statement should be:

SELECT * FROM book_mst WHERE BookBarCode = '0123456789'

edited Apr 05 '15 at 18:47

Chris Baker

49,926
12
96
115

answered Apr 05 '15 at 12:19

lulco

524
2
11

This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. – d_inevitable Apr 05 '15 at 16:16
@d_inevitable actually, it did answer the question. It just needed some clarification. Lulco, in future answers, just remember that less isn't more. Your answers are both to help this person, and to teach someone else in the future with a similar problem. – Chris Baker Apr 05 '15 at 18:49
I don't think this answers the question at all. You have merely corrected the example `SELECT` query in the OP. The OP could have been better framed. But, what the question means is, how do I pass a value (_in this case, a barcode number_) from Android to a PHP file to complete the `SELECT` query where the `WHERE` part of the query is dynamic. Correcting a typo, in this case, is hardly a solution. – Siddharth Lele Apr 06 '15 at 04:24
@SiddharthLele it isn't a *good* answer, or a complete answer, but it is an answer. Not worthy of deletion. There's an active meta topic about this very sort of thing. – Chris Baker Apr 06 '15 at 06:46
@ChrisBaker: Oh absolutely. I am merely commenting to point out what is I think is required by the OP based on the fact that the OP has already posted two PHP codes. One for a select query and one to insert a row. Based on that, I figured that the answer is wrong to the extent that it does not address the actual question. And, I personally _have neither flagged it nor downvoted the answer_. – Siddharth Lele Apr 06 '15 at 06:52

MySQLi and PHP: Combine a SELECT query with a WHERE clause

1 Answers1