MYSQL not working with Where

Question

I am trying to get data from a table to show in table manner.

$myid = 'First_User';
$checkQuery = "SELECT * FROM MyDatabase WHERE id='" . $myid . "';";
echo $checkQuery; // it returns SELECT * FROM MyDatabase WHERE id='First_User';. I run in mySQL console , and it returns the record.
$result = mysql_query($checkQuery);     
$row = mysql_fetch_array($result);

The above seems not able to fetch anything. But says "Resource #3". No data seen.

$myid = 'First_User';
$checkQuery = "SELECT * FROM MyDatabase";

$result = mysql_query($checkQuery);
$row = mysql_fetch_array($result);

This works returning all data from MyDatabase. I do have First_User in MyDatabase.

Please don't learn database access using the old, deprecated MySQL extension; learn to use MySQLi or PDO with prepared statements/bind variables — Mark Baker, Oct 01 '14 at 08:48
What are you displaying to get `Resource #3`, sounds like you're displaying `$result`; you need to display the `$row` array — Mark Baker, Oct 01 '14 at 08:49
yeap. id column has First_User. Wondering what is Resource#3. — Khine S Z, Oct 01 '14 at 08:52
Maybe because `id`'s structure is `int` and you try to look for `First_User` string from it. And if there is really `First_User` value, is it really in the `id` column? — Logan Wayne, Oct 01 '14 at 08:53
print_r returns Array ( [0] => First_User [id] => First_User [1] => This is Address. [address] => This is Address. ) — Khine S Z, Oct 01 '14 at 08:54
@Kay please show the code where you're actually displaying the results, everything else seems fine, especially if your print_r returns that.. — andyroo, Oct 01 '14 at 08:58
while($row = mysql_fetch_array($result)) { echo $row[0]; echo ""; echo ""; echo ""; } — Khine S Z, Oct 01 '14 at 09:03
sorry. very new to posting. :) when i echo $row[0] , nothing comes up. btw, how to go next line in this post. Pressing enter auto do "Add Commen." — Khine S Z, Oct 01 '14 at 09:04

score 0 · Answer 1 · answered Oct 01 '14 at 08:51

0

Hi MySQL is table Orientated.

Select <table felds> , < table felds> From <table> Where ....

answered Oct 01 '14 at 08:51

Denis Kohl

739
8
13

It looks like their table is called "MyDatabase" - the SELECT * works when omitting the WHERE clause. – danmullen Oct 01 '14 at 08:54

score 0 · Answer 2 · answered Oct 01 '14 at 08:55

The problem is the way you are trying to query, basically you have to do:

SELECT * FROM your table_name WHERE field_name='Field_value';

The database is usually defined on the driver you use to connect to the database in this case the deprecated

mysql-connect But as already mentioned you should use PDO is newer and safer :)

score 0 · Answer 3 · answered Oct 01 '14 at 09:00

0

Simply Try this ..

$checkQuery = "SELECT * FROM MyDatabase WHERE id='$myid'";

answered Oct 01 '14 at 09:00

sameer kumar

149
2
3
13

i try $checkQuery = "SELECT * FROM MyDatabase WHERE id='$myid'";. same no data fetched – Khine S Z Oct 01 '14 at 09:08
Use a text box at your front end webpage and take the data from user and display it in the web page Like this: – sameer kumar Oct 01 '14 at 09:22
if(isset($_REQUEST['Your button name'])){$text = $_REQUEST("Your Text box name"); $checkQuery = "SELECT * FROM MyDatabase WHERE id='$myid'"; $query=mysql_query($checkQuery);} and than echo the query .. – sameer kumar Oct 01 '14 at 09:23

score 0 · Answer 4 · answered Oct 01 '14 at 09:11

Try this -

$myid = 'First_User';
$checkQuery = "SELECT * FROM MyDatabase WHERE id='$myid'";
$result = mysql_query($checkQuery);
$row = mysql_fetch_array($result);
while($row = mysql_fetch_array($result)) {
    echo '<td><input type="text" name="ID" value='.$row[0].'></td>';
}

Note : Avoid using mysql_* functions. Use instead mysqli_* functions.

score 0 · Answer 5 · edited May 23 '17 at 12:03

Firstly, don't use mysql_connect, use MySQLi or PDO

I prefer PDO.

How to connect to PDO:

$db_connection = new PDO('mysql:host='. DB_HOST .';dbname='. DB_NAME . ';charset=utf8', DB_USER, DB_PASS);

Now you want to write your query and fetch

$query = $db_connection->prepare("SELECT * FROM `MyDatabase` WHERE `id` = :id");
$query->execute(array(":id" => $myid));
$result = $query->fetchAll();

foreach($result as $user) {
    echo $user['id'];
    echo "<tr>";
    echo "<td><input type=\"text\" name=\"ID\" value=\"" echo $row['id'] "\" /></td>";
    echo "</tr>"
}

I hope I havne't missed anything off, but please read PDO documentation: http://php.net/manual/en/book.pdo.php (or mysqli: http://php.net/manual/en/book.mysqli.php )

And if you're using post data somewhere in your query, please see other posts on how to prevent mysql injection: How can I prevent SQL injection in PHP?

Syed Arif Iqbal · Answer 6 · 2014-10-01T09:46:43.797

0

Use It. And Additional Add LIMIT because your query will return only result. It's extra potential.

$id = $_SESSION['user_id']; // or something else.  

$checkQuery = "SELECT * FROM MyDatabase WHERE id='$id' LIMIT 1";

$results = mysqli_query($checkQuery);

if ($result) $user=mysql_fetch_assoc($result);

// use now.

echo $user['user_name'];
echo $user['id'];

edited Oct 01 '14 at 09:46

answered Oct 01 '14 at 09:33

Syed Arif Iqbal

1,830
12
16

MYSQL not working with Where

6 Answers6