connect Php to mysql and retrieve data

Question

bangla 0 Unverified User

13 Years Ago

Hi,

I am a beginner in php and mysql. Someone pls be kind to help me the following if possible.

1. I know the host name, database name, table name
2. The table has 3 fields-State, Name, Specialty.
3. I want to have a drop down list name ‘State’. In the State list, all the states will be listed.
4. Once user select ‘NJ’ from the drop list and hit submit, I want it to go to database>table, and pull up all records equal to ‘NJ’.
--------------

I am just playing with the code. Below code I have. I know I am not near to what I want to accomplish.
-----------------------
<?php
mysql_connect($hostname,$username, $password) or die
("<html><script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script></html>");

mysql_select_db($dbname);

$query = "SELECT State, Name, Specialty FROM $usertable";
$result = mysql_query($query);
?>

<!- HTML form opening tags -->
<form action="action" method="post">
<select name="option">

<?php

//for each row we get from mysql, echo a form input
while ($row = mysql_fetch_array($result)) {
echo "<option Name=\"$row[Name]\">$row[Specialty]</option>\n";
}
?>

<!- HTML form closing tags -->
</select>
<input type="submit">
</form>
---------------------

Pls advise.Thx.

mysql php

3 Contributors
10 Replies
214 Views
3 Days Discussion Span
Latest Post 13 Years Ago Latest Post by twiss

twiss 155 Veteran Poster

13 Years Ago

Okay, some hints.
- Use code tags :)
- This doesn't exactly matter, but your select name doesn't make sense. You better rename it to something else (perhaps state, but you're using name?).
- Options have a value, not a name. (Perhaps you also mean $row[State]? But I'm not sure about that.)
- You'll need to run another query, something like this:

$query = "SELECT State, Name, Specialty FROM $usertable WHERE Name = '".mysql_real_escape_string($_POST['the_name_of_your_select'])."'";

twiss 155 Veteran Poster

13 Years Ago

That last example won't work. It should be:

$result = mysql_query($query = "SELECT Name, Specialty FROM $usertable WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'");

Or just:

$result = mysql_query("SELECT Name, Specialty FROM $usertable WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'");

Edited 13 Years Ago by twiss because: n/a

twiss 155 Veteran Poster

13 Years Ago

Example: http://www.anyexample.com/programming/php/php_mysql_example__display_table_as_html.xml

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

bangla 0 Unverified User · Answer 1 · 2011-06-08T09:18:32+00:00

Followings i did. I got close. still need help.

1. i created a file "caller.php"
<html>
<body>

</body>
</html>

2. mysqlpractice3.php
<?php

$hostname="localhost";
$username="try1";
$password="alal12";
$dbname="dbname";
$usertable="aa";
$yourfield = "Specialty";
$con = mysql_connect("localhost","$username","$password");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("dbname", $con);

$query = "SELECT Name, Specialty FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST)."'";
echo $query;
mysql_close($con);
?>

4. inserted a row. "NJ" exist in database table under name column

5. when i run caller.php,i get the form where i entered "NJ" in name field then hit "submit query"

6. I get answer as is :
SELECT Name, Specialty FROM WHERE Name = 'NJ'.

its not pulling the data from database. i want to retrieve rows from database where name = NJ.
Pls advise. thx.

masterjiraya -3 Junior Poster · Answer 2 · 2011-06-08T09:47:49+00:00

you used a search query

$query = "SELECT Name, Specialty FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'";
echo $query;
mysql_close($con);

the error was this ... echo function calls a variable and since it is enclosed in double quotes what ever was inside double quotes will considered as a string and NOT a query or a process... funny isn't it LOL :))

since you want to echo what ever it is inside the $query I did it for you this way...

$query = "SELECT Name, Specialty FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'";
//echo $query; <-- don't echo $query
//here is the proper echoing $query
// use mysql_fetch_array function
while($DataItem=mysql_fetch_array($query)){
//here is the proper echo format what ever you want
echo "State is ".$DataItem['State']." with the name ".$DataItem['Name']." and his/her specilaty is ".$DataItem['Specialty']."<br/>";
}
mysql_close($con);

since you said it that it has only 3 fields then here it goes why I coded

$DataItem['State'], $DataItem['Name'] and $DataItem['Specialty']

review this mysql_fetch_array... as I code the $query this is the proper echo retrieving data function syntax of PHP with mySQL server.. the other one is mysql_num_rows function. the difference is instead of

$DataItem['State'], $DataItem['Name'] and $DataItem['Specialty']

it goes this way

$DataItem[0], $DataItem[1] and $DataItem[2]

which has only same results I hope this one is the way you want it

bangla 0 Unverified User · Answer 3 · 2011-06-08T10:58:53+00:00

$query = "SELECT Name, Specialty FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST)."'";

//echo $query; <-- don't echo $query

//here is the proper echoing $query

// use mysql_fetch_array function

while($DataItem=mysql_fetch_array($query)){

//here is the proper echo format what ever you want

echo " with the name ".$DataItem." and his/her specilaty is ".$DataItem."<br/>";

}

mysql_close($con);

I tried the code but i got the warning. Now I am focusing two columns. name and specialty.

once i run the code, i get error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...line 26

line 26: while($DataItem=mysql_fetch_array($query)){

Thx a lot.

masterjiraya -3 Junior Poster · Answer 4 · 2011-06-08T13:42:00+00:00

I will fix it for you... sorry for a long wait... here's what you do

first after you do the ...

$query = "SELECT Name, Specialty FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'";

the next thing to do is compile the syntax by typing mysql_query function... this is the proper syntax

//create new variable
$query2=mysql_query($query) or die('cannot do the query in $query2 '.mysql_error($query2));

I'll explain this (the thing I've done above) to you later

then type this revised code

while($DataItem=mysql_fetch_array($query2)){

//here is the proper echo format what ever you want

echo " with the name ".$DataItem['Name']." and his/her specilaty is ".$DataItem['Specialty']."<br/>";

}

mysql_close($con);

that's all about it... the thing I have done in the $query2=mysql_query($query) it goes like this...

you created a variable $query with an action script of search query in the database... however it's not yet being done or compiled so you have to create another variable called $query2 with an action script that will call the function mysql_query($query), this function is a type of function that will call the compiler of mySQL, whereas mysql_query calls what ever script was inside the parenthesis meaning to say, you can do it also this way...

mysql_query("$query = "SELECT Name, Specialty FROM $usertable WHERE Name = '".mysql_real_escape_string($_POST['fname'])."'";

by means of symmetrical formula... just like a=1 and b=a so b=1 because 'a' is having a value of '1' :)

the die() is a function error event terminator that will reveal a message error that is understandable by common users.

bangla 0 Unverified User · Answer 5 · 2011-06-09T23:10:46+00:00

Below code helped:--------

$query = "SELECT Name, Specialty, Address, Contact FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST)."'";

$query2=mysql_query($query) or die('cannot do the query in $query2 '.mysql_error($query2));//create new variable

//echo $query; <-- don't echo $query

//here is the proper echoing $query

// use mysql_fetch_array function

while($DataItem=mysql_fetch_array($query2)){

//here is the proper echo format what ever you want

echo " Name: ".$DataItem." and Specialty: ".$DataItem." Address: ".$DataItem." Contact ".$DataItem."<br/>"; }

mysql_close($con);

result:i entered SHIPU

i got:
Name: shipu and Specialty: criminal Address: arlington,va Contact 12345678
Name: shipu and Specialty: malpractice Address: rosslyn,va Contact 22345678

thanks a lot.

next question: how can i get the result in table format. with column headings with row and column?

If you have time.

Many thanks.

bangla 0 Unverified User · Answer 6 · 2011-06-10T03:32:29+00:00

i went to the url. then made some changes on my code. i got below:

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

$query = "SELECT Name, Specialty, Address, Contact FROM $usertable
WHERE Name = '".mysql_real_escape_string($_POST)."'";

$query2=mysql_query($query) or die('cannot do the query in $query2 '.mysql_error($query2));//create new variable

$fields_num = mysql_num_fields( $query);
echo "<h1>Table: {$usertable}</h1>";
echo "<table border='1'><tr>";
for($i=0; $i<$fields_num; $i++)
{
$field = mysql_fetch_field( $query);
echo "<td>{$field->name}</td>";}
echo "</tr>\n";
while($row = mysql_fetch_row( $query)){
echo "<tr>"; foreach($row as $cell)
echo "<td>$cell</td>";
echo "</tr>\n";
}
mysql_free_result( $query);
?>
</body></html>

-----------
if i run it, i get the below error:
Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in ........ on line 29

Table: lawyer

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in ............. line 37

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in ............... line 42

--------
If you have time, pls advise. thanks.

twiss 155 Veteran Poster · Answer 7 · 2011-06-10T11:45:35+00:00

You should change $query2 to $result (that's what it is) and in the rest of the code, change $query to $result. And could you use code tags?