homepage Welcome to WebmasterWorld Guest from 54.211.201.65
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP & My SQL Query
krishna




msg:3807273
 6:37 pm on Dec 14, 2008 (gmt 0)

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




msg:3807393
 9:11 pm on Dec 14, 2008 (gmt 0)

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




msg:3807614
 4:38 am on Dec 15, 2008 (gmt 0)

I am getting the following o/p

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

Mahabub




msg:3807655
 6:58 am on Dec 15, 2008 (gmt 0)

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




msg:3807658
 7:27 am on Dec 15, 2008 (gmt 0)

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

krishna




msg:3808366
 4:38 am on Dec 16, 2008 (gmt 0)

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




msg:3808442
 9:07 am on Dec 16, 2008 (gmt 0)

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




msg:3808564
 1:56 pm on Dec 16, 2008 (gmt 0)

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




msg:3808736
 6:14 pm on Dec 16, 2008 (gmt 0)

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




msg:3808767
 6:48 pm on Dec 16, 2008 (gmt 0)

It worked !

Thanks for that quick reply.

krishna




msg:3811764
 5:54 am on Dec 20, 2008 (gmt 0)

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

Mahabub




msg:3811765
 6:04 am on Dec 20, 2008 (gmt 0)

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




msg:3811955
 7:33 pm on Dec 20, 2008 (gmt 0)

<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




msg:3819698
 7:12 pm on Jan 5, 2009 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved