PHP & My SQL Query

[krishna]

Guys i have a php & mysql script for searching data written by a friend.
Can some make necessary changes so that the output appears as metioned at the end.

<?php
if (!$conn = mysql_connect('hostname', 'username', 'password'))
die("Error: Cannot Establish Connection to Database");

if (!mysql_select_db('database_name'))
die("Error: Database doesn't exist");

?>

<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<label>Enter ID: <input type="text" name="id" /></label>
<input type="submit" value="Search" />
</form>
<br /><br />

<?php

if (isset($_GET['id']) && $_GET['id'] != NULL)
{
$result = mysql_query("SELECT * FROM table_name WHERE id LIKE " . $_GET['id']);
if ($result)
{
$resultSet = mysql_fetch_assoc($result); //retrieves all the information as a assocated array

echo "<table><tr><th>ID</th><th>Name</th><th>Result</th></tr>";
foreach ($resultSet as $row)
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['result'] . "</td>";
echo "</tr>";
}
echo "</table>";
}
else
{
echo "Error: Something wrong with Query";
}
}

mysql_close($conn);
?>

The o/p is as follows
ID Name Result
8 8 8
S S S
Y Y Y

The data in the db is as follows:
Id name result
8001 xyz passed
8002 abc failed

How can i get the o/p which is some thing like this

Dear [name] you are [result].

[tr8er8]

replace:

foreach ($resultSet as $row)
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['result'] . "</td>";
echo "</tr>";
} 

with:

foreach ($resultSet as $row)
{
echo "Dear ".$row['name'].", you are ".$row['result'];
}

[krishna]

I am getting the following o/p

Dear 8, you are 8Dear A, you are ADear Y, you are Y

[Mahabub]

Dear krishna,

Try the below one..........and let us know.:d

<?php
if (!$conn = mysql_connect('hostname', 'username', 'password'))
die("Error: Cannot Establish Connection to Database");

if (!mysql_select_db('database_name'))
die("Error: Database doesn't exist");

?>

<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<label>Enter ID: <input type="text" name="id" /></label>
<input type="submit" value="Search" />
</form>
<br /><br />

<?php

if (isset($_GET['id']) && $_GET['id'] != NULL)
{
$result = mysql_query("SELECT * FROM table_name WHERE id LIKE " . $_GET['id']);
if ($result)
{
echo "<table><tr><th>ID</th><th>Name</th><th>Result</th></tr>";

while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['result'] . "</td>";
echo "</tr>";

}

echo "</table>";
}
else
{
echo "Error: Something wrong with Query";
}
}

mysql_close($conn);
?>

Thanks
Mahabub

[krishna]

Hey it worked !
Thanks Mahabub.
How do i print a enrol id not found message if the id doesnt exist.

[krishna]

Guys do i need to change any code in the script for searching via Name instead of ID.

I want to enter the name and get the id and result.

[Mahabub]

Dear krishna,

check the number of rows returned by the query if its is 0. then ID doesnt exisit.

if(mysql_num_rows($result)==0){
// ID not found in DB
}

----------------------------------------------------------------

yes you need to change in your code the changes are given below:

<label>Enter ID: <input type="text" name="name" /></label>

if (isset($_GET['name']) && $_GET['name'] != NULL)

$result = mysql_query("SELECT * FROM table_name WHERE name LIKE " . $_GET['name']);

Thanks
Mahabub

[krishna]

i am getting Error: Something wrong with Query
If i enter a word (name) then i get the above error, but if i enter a number then i get the field names.

<?php
if (!$conn = mysql_connect('localhost', '#*$!', '000'))
die("Error: Cannot Establish Connection to Database");

if (!mysql_select_db('abc'))
die("Error: Database doesn't exist");
?>
<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>">
<label>Enter ID: <input type="text" name="name" /></label>
<input type="submit" value="Search" />
</form>
<br /><br />

<?php

if (isset($_GET['name']) && $_GET['name'] != NULL)
{
$result = mysql_query("SELECT * FROM aaa WHERE name LIKE " . $_GET['name']);
if ($result)
{
echo "<table><tr><th>ID</th><th>Name</th><th>Result</th></tr>";
while ($row = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<td>" . $row['result'] . "</td>";
echo "</tr>";
}
echo "</table>";
}
else
{
echo "Error: Something wrong with Query";
}
}

mysql_close($conn);
?>

[Mahabub]

Dear krishna,

Your sql format is not right. I mean problem in the below line
$result = mysql_query("SELECT * FROM aaa WHERE name LIKE " . $_GET['name']);

Instead of that use this one
$result = mysql_query("SELECT * FROM aaa WHERE name LIKE '".$_GET['name']."'");

Think it will work

Thanks
Mahabub

[krishna]

It worked !

Thanks for that quick reply.

[krishna]

Guys is it possible to search with two fields.
i.e. if the two fields match exactly then a third field be called.

[Mahabub]

Dear krishna,

It is possible to search with multple fields. Like

SELECT * FROM table_name WHERE field1='test' AND field2='TEST2' OR field3 = 'TEST3'

Thanks
Mahabub

[krishna]

<html dir="ltr">
<head>
</head>
<?php
if (!$conn = mysql_connect('localhost', '#*$!', 'aaa'))
die("Error: Cannot Establish Connection to Database");

if (!mysql_select_db('#*$!'))
die("Error: Database doesn't exist");

?>
<body>
<p>&nbsp;</p>
<?php
if (!$conn = mysql_connect('localhost', '#*$!', 'aaa'))
die("Error: Cannot Establish Connection to Database");

if (!mysql_select_db('abc'))
die("Error: Database doesn't exist");

?>
<h4>Enter your password entered at the time of submission</h4>
<form method="get" action="<?php echo $_SERVER['PHP_SELF']; ?>
<label>Enter Name : <input type="text" name="field_1" /></label>
<label>Enter Password : <input type="text" name="field_2" /></label>
<input type="submit" value="Search" />
</form>
<?php

if (isset($_GET['field_1']) && $_GET['field_1'] != NULL)
{
$result = mysql_query("SELECT * FROM #*$! WHERE field_1 LIKE '".$_GET['field_1']."'");
if ($result)
{
echo "<table border='1'>";
echo "<tr><th>Name</th><th>Name</th><th>Address</th></tr>";

while ($row = mysql_fetch_assoc($result)) {
echo "<tr><td>";
echo $row['field_3'];
echo "</td><td>";
echo $row['field_4'];
echo "</td></tr>";

}

echo "</table>";
}
else
{
echo "Error: Something wrong with Query";
}
}

mysql_close($conn);
?>
</body>
</html>

What do i change in the sql query ?

[krishna]

Can some one help me out !
How can we search using two fields as said above ?
suppose i have 10 fields and i want to seach using the 1st and the 5th field, if both match then the query should show another field.