Mysql Query, comparing values and assigning to PHP variables

Question

I have done a fair bit of research into what i want to do, although i haven't found anything. I am not too sure if i am looking for the right thing :( I am also a little bit new to PHP and MySQL syntax, so please be kind.

I wish to perform the following in this order:

Connect to a database (DONE)
Query for a specific string (I think im done) From here is gets a bit fuzzy :(
If a match is found for the variable, copy the whole row (I need other variables).
Assign the values from the SQL query to a PHP variables.
From there i will be right to carry on.

I have established the connection to the database with the following:

function connect() {
$dbname = 'database';
$dbuser = 'username';
$dbpass = 'password';
$dbhost = 'localhost';
mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to connect to database");
}

And then calling the function connect();

I then wish to query the database for a particular value, for the sake of this argument i will use a static value. This is what i have:

mysql_select_db(DATABASENAME) or die( "Unable to select database");
$query = "SELECT * FROM `TABLE` WHERE `COLUMN` LIKE 'VAULE'";
$result=mysql_query($query);

From here i am not too sure how to compare the query result to see if it is a match (something along the lines of mysql rows?). If there is a match, then i would like to obtain the entire row, and assign each value to a php variable.

I am not asking for you to do it for me, simply i kick in the right direction should be fine!

Hope it explains it enough :)

Thanks for your kind guidance

This may not be a production system, but I still would not have posted the password... — Kris.Mitchell
– Kris.Mitchell, Commented Mar 29, 2012 at 12:23
Kris,Thanks the passwords and usernames are not real, i have just made them up for context purposes :) — user1300680
– user1300680, Commented Mar 30, 2012 at 8:18

Kris.Mitchell · Accepted Answer · 2012-03-29 12:22:29Z

Ok. You will want to keep the connection to the mysql database somewhere. A common use is $conn.

So you would have

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to connect to database");

Then, either from the URL or Post, or just some variables you have sitting in your php file, you can query the database by putting the variables in the query itself. Also, here you can use $conn so that you have one place to connect to the database, in an include for example, and you won't have to make all of the connection string in each place you need to connect to the DB.

$query = "SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%" . $varToCompare . "%'";
$result=mysql_query($query,$conn);

Above you are using a like. You may want to just look at doing .. Where column=$var.

Then you can use php to spin through the results into an array (for queries where would get multiple rows).

tereško · Accepted Answer · 2012-03-29 12:28:18Z

1

Where the hell you learned how to use MySQL in PHP ? The mysql_* functions are more then 10 years old and not maintained anymore. Community has already begun to work on deprecating them.

You should be using PDO or MySQLi for that.

// connection to database
$db = new PDO('mysql:host=localhost;dbname=datadump_pwmgr;charset=UTF-8', 
               'datadump_pwmgr', 
               'kzddim05xrgl');
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

// setting up prepared statement for the query
$statement = $db->prepare('SELECT * FROM table WHERE column LIKE :value');
$statement->bindParam(':value', $some_variable, PDO::PARAM_STR, 127);

// executing query and fetching first result
if ( $statement->execute())
{
    $data = $statement->fetch(PDO::FETCH_OBJ);
    var_dump( $data );
}

This should give you something like what you needed. Though, I would recommend to try this tutorial. And learning more about prepared statements could be useful too.

Also , if you are working with objects, then it is possible to create a single DB connection object , and pass it to multiple other classes to use it:

$pdo = new PDO('sqlite::memory:');

$a = new Foo( $pdo );
$b = new Bar( $pdo, 'something');

This way you pass both objects the same database connection, and you do not need to reinitialize it.

edited Mar 29, 2012 at 12:28

answered Mar 29, 2012 at 12:19

tereško

58.5k26 gold badges100 silver badges151 bronze badges

2 Comments

user1300680 Over a year ago

I had to start somewhere, i am new to stuff like this and had no idea what PDO was. I have used mysqli a little before, but just used mysql in this example.

user1300680 Over a year ago

I am now looking into PDO as it looks much more cleaner!

Bono · Accepted Answer · 2012-03-29 12:15:18Z

0

I think you're looking for something like this:

$count = mysql_num_rows($result);

//if there is more then 1 record retrieved from the database
if($count > 0)
{
    //Do what ever you want to do here, which I think you want to be
    while ($row = mysql_fetch_assoc($result))
    {
        echo $row["Columnname1"];
        echo $row["Columnname2"];
        echo $row["Columnname3"];
    }
}

else
{
    echo "There are no matches for this specific value";
}

answered Mar 29, 2012 at 12:15

Bono

4,8878 gold badges52 silver badges78 bronze badges

2 Comments

Bono Over a year ago

As teresko point out though, you are better of using PDO (for example), since prepared statements are a great way to avoid SQL injection.

user1300680 Over a year ago

Thank you this makes a lot more sense to me, i will have a look into PDO though Bono.

dmorlock · Accepted Answer · 2012-03-29 12:17:51Z

0

You can get the queried data by rows as an associated array using mysql_fetch_array():

$row = 0;
$data = mysql_query("SELECT name1,name2 FROM ....");
while(($result = mysql_fetch_array($data)) !== false)
{
    echo "row = $row, name1 = " . $result["name1"] . ", name2 = " . $result["name2"];
    $row ++;
}

... or as an objects using mysql_fetch_object():

$row = 0;
$data = mysql_query("SELECT name1,name2 FROM ....");
while(($result = mysql_fetch_object($data)) !== false)
{
    echo "row = $row, name1 = $result->name1, name2 = $result->name2";
    $row ++;
}

answered Mar 29, 2012 at 12:17

dmorlock

2,0414 gold badges20 silver badges22 bronze badges

Comments

haltabush · Accepted Answer · 2012-03-29 12:18:29Z

I'm not too sure of what you want, but I can see one probable bug here: you're using LIKE in a way which means =: in order to have LIKE to behave like a like, you need some joker chars :

"SELECT * FROM `TABLE` WHERE `COLUMN` LIKE 'VAULE'" // This will return all rows where column='VAUL'
"SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%VAULE%'" // This will return all rows where column='%VAUL%' // This will return any row containing 'VAUL' in column
"SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%VAULE'" // This will return all rows where column='%VAUL' // this will  return all rows ending by VAUL. I guess you get it now :)

An to retrieve the actual results:

$query = "SELECT * FROM `TABLE` WHERE `COLUMN` LIKE '%VAULE%'";
$result=mysql_query($query);

while (false !== ($row = mysql_fetch_assoc($result))) {
    //here $row is an array containing all the data from your mysql row
}

vvr · Accepted Answer · 2012-03-29 12:13:58Z

-1

Try to write the database connection in another page no need to use function and include that page in where ever you need.

ex: require_once 'dbConnect.php';

dbConnect.php consists:

<?php
$dbname = 'datadump_pwmgr';
$dbuser = 'datadump_pwmgr';
$dbpass = 'kzddim05xrgl';
$dbhost = 'localhost';
mysql_connect($dbhost, $dbuser, $dbpass) or die("Unable to connect to database");
?>

answered Mar 29, 2012 at 12:13

vvr

4663 gold badges17 silver badges30 bronze badges

1 Comment

vvr Over a year ago

may be i didn't get your question properly.

Collectives™ on Stack Overflow

Mysql Query, comparing values and assigning to PHP variables

6 Answers 6

Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

2 Comments

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related