Database Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Codewalkers Forums > Other Technologies > Database Help
Reload this Page

need help getting data to display

Discuss need help getting data to display in the Database Help forum on Codewalkers.
need help getting data to display General SQL help. Help with MySQL, MSSQL, Oracle, mSQL, PostgreSQL, etc.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Codewalkers Forums Sponsor:
  #1  
Old August 15th, 2003, 01:12 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
need help getting data to display
first let me say I am not a coder... I was partnered wit a coder to complete a small project that is similar to a yellow page application... my obligation was to compile and format over 40,000 records that will populate the database... my problem is the coder I was partnered with has walked away leaving me with something I know very little about... I am hopine someone here with the needed knowledge will assist me with the following script...

The script display two drop down menu selection... one to select a "city" and another to select a business "category"

the script does that (althought there is a problem with the "category" menuy as it display two of each category???)... What I need is someone to help me get the data to display when the script is executed...
i.e., display the list of businesses (selected category)(name, address,city, zip, phone, etc) that are in the city also selected ... below is the script followed by the table data... TIA to anyone that can help me out here....

<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
/*
broke this into functions...much easier to see and follow the flow of the program
*/

/* main code block */
//see if the form is submitted
if (!$_POST['submit']){
show_form();
}else{
//form submitted and need to get record(s)
get_data();
}//end if

//end main code block

function show_form()
{
//assume that city and category come from database


mysql_connect("localhost", "######", "######") or die(mysql_error());
mysql_select_db("BUSLIST") or die(mysql_error());


//get data for city and category
$sql = 'SELECT DISTINCT city FROM bus_list';
$r = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_object($r))
{
$city[] = $row->city;
}

$sql = 'SELECT DISTINCT category FROM bus_list';
$r = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_object($r))
{
$category[] = $row->category;
}

$result_city = mysql_query($sql) or die ("Can't connect to perform query one because ".mysql_error());

$result_category = mysql_query($sql) or die ("Can't connect to perform query two because ".mysql_error());

if ((!result_city)||(!$result_category)){
echo "error retrieving data";
}else{
//place the results into an array
while ($rows=mysql_fetch_array($result_city )){
$city[]=$rows['city'];
}

while ($rows1=mysql_fetch_array($result_category)){
$category[]=$rows1['category'];
}


//show the form headers
echo "
<body>
<form action="" . $_SERVER['PHP_SELF'] . "" method="post">
<table>
<tr>
<td>Select city</td></td>
<td>Select category</td>
</tr>
<tr>
<td><select name="city">";

//show the city data
for ($x=0;$x<count($city);$x++){
echo "<option value="".$city[$x]."">".$city[$x];
}
echo "</select></td><td>
<select name="category">";

//add the category data
for ($x=0;$x<count($category);$x++){
echo "<option value="".$category[$x]."">".$category[$x];
}
echo "</select></td></tr></table>
<input type="submit" name="submit" value="search">
</form></body>";
}
}//end function show_form

function get_data()
{
//get data from form
$city=$_POST['city'];
$category=$_POST['category'];

$sql="select * from bus_list where city='$city' and category='$category'";

mysql_connect("localhost", "######", "######") or die(mysql_error());
mysql_select_db("BUSLIST") or die(mysql_error());

$result=mysql_query($sql) or die ("can;t query because ".mysql_error());

//show results to user
}
//end function get_data
?>
</body>

----------------------------------------------------------------------------------------------------------
CREATE TABLE `bus_list` (
`name` varchar(255) NOT NULL default '',
`address` varchar(255) NOT NULL default '',
`city` varchar(255) NOT NULL default '',
`zip` varchar(15) default NULL,
`phone` varchar(40) default NULL,
`www` varchar(255) default NULL,
`notes` text,
`cat` varchar(255) default NULL,
`id` int(11) unsigned NOT NULL auto_increment,
`subcatid` varchar(11) default NULL,
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;
Reply With Quote
  #2  
Old August 15th, 2003, 01:27 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: need help getting data to display
as far as dups in the dropdown, you are running the queries twice and adding both resuts sets to the same array.

here is corrected showform code
php Code:
Original - php Code 
  1.  
  2. <?
  3. function show_form() 
  4. { 
  5. //assume that city and category come from database 
  6.  
  7.  
  8. mysql_connect("localhost", "######", "######") or die(mysql_error()); 
  9. mysql_select_db("BUSLIST") or die(mysql_error()); 
  10.  
  11.  
  12. //get data for city and category 
  13. $sql_city = 'SELECT DISTINCT city FROM bus_list';
  14.  
  15. $sql_catagory = 'SELECT DISTINCT category FROM bus_list';
  16.  
  17. $result_city = mysql_query($sql_city) or die ("Can't connect to perform query one because ".mysql_error()); 
  18.  
  19. $result_category = mysql_query($sql_catagory) or die ("Can't connect to perform query two because ".mysql_error()); 
  20.  
  21. if ((!$result_city)||(!$result_category)){ 
  22. echo "error retrieving data"; 
  23. }else{ 
  24. //place the results into an array 
  25. while ($rows=mysql_fetch_array($result_city )){ 
  26. $city[]=$rows['city']; 
  27. } 
  28.  
  29. while ($rows1=mysql_fetch_array($result_category)){ 
  30. $category[]=$rows1['category']; 
  31. } 
  32.  
  33. //show the form headers 
  34. echo " 
  35. <body> 
  36. <form action="" . $_SERVER['PHP_SELF'] . "" method="post">
  37. <table> 
  38. <tr> 
  39. <td>Select city</td></td> 
  40. <td>Select category</td> 
  41. </tr> 
  42. <tr> 
  43. <td><select name="city">"; 
  44.  
  45. //show the city data 
  46. for ($x=0;$x<count($city);$x++){ 
  47. echo "<option value="".$city[$x]."">".$city[$x]; 
  48. } 
  49. echo "</select></td><td> 
  50. <select name="category">"; 
  51.  
  52. //add the category data 
  53. for ($x=0;$x<count($category);$x++){ 
  54. echo "<option value="".$category[$x]."">".$category[$x]; 
  55. } 
  56. echo "</select></td></tr></table> 
  57. <input type="submit" name="submit" value="search"> 
  58. </form></body>"; 
  59. } 
  60. }//end function show_form 
  61. ?>


That should correct the dropdown problem (note the code is untested but should work)

lets get that resolved and then move on to the next step...
Reply With Quote
  #3  
Old August 15th, 2003, 01:37 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: need help getting data to display
Hi and thanks for the reply... I tried your code.. not sure where but there seems to be something missing? (;, }) or something because I get an error citing the last line of the script???
Reply With Quote
  #4  
Old August 15th, 2003, 01:40 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: need help getting data to display
take out the last curly bracket in the function after the body tag, that should fix that problem.
Reply With Quote
  #5  
Old August 15th, 2003, 01:46 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: need help getting data to display
here is the get_data function modified slightly to show you how to display the data.
php Code:
Original - php Code 
  1.  
  2.  
  3. <?
  4. function get_data() 
  5. { 
  6. //get data from form 
  7. $city=$_POST['city']; 
  8. $category=$_POST['category']; 
  9.  
  10. $sql="select * from bus_list where city='$city' and category='$category'"; 
  11.  
  12. mysql_connect("localhost", "######", "######") or die(mysql_error()); 
  13. mysql_select_db("BUSLIST") or die(mysql_error()); 
  14.  
  15. $result=mysql_query($sql) or die ("can;t query because ".mysql_error()); 
  16.  
  17. echo "<table>"
  18.  
  19. while ($row=mysql_fetch_array($result){
  20. echo"<tr><td>".$row['name']."</td><td>".$row['address']."</td><td>".$row['city']."</td><td>" //etc...etc
  21.  
  22.  
  23. }
  24. echo "</table>"
  25.  
  26. //show results to user 
  27. } 
  28. //end function get_data 
  29. ?>


if you just follow my lead inside the while statement, and just add the rest of the column names to the echo, it should output a table row for each record returned. Since my design skills are quite bad, I will leave the formatting up to you. Let me know how it goes!
Reply With Quote
  #6  
Old August 15th, 2003, 01:48 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: need help getting data to display
please bear with me here I am not a coder... I am stuck trying to do something that is beyond my experience and I am having a hard time with this...

I went back to my original code and commented out the second string that was causing the double population... and that worked...

now Can you help me get the data to display?

TIA
Reply With Quote
  #7  
Old August 15th, 2003, 01:59 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: need help getting data to display
see my post previous to your last post, it explains it there!!!
Reply With Quote
  #8  
Old August 15th, 2003, 02:01 PM
Anonymous Anonymous is offline
Registered User
Codewalkers God 35th Plane (22000 - 22499 posts)
  
Join Date: Apr 2007
Posts: 22,309 Anonymous User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 24
RE: need help getting data to display
getting "Parse error: parse error, expecting `','' or `';''... on line 110"

here is the entire (edited) script...what am I doing wrong? TIA

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<?php
/*
broke this into functions...much easier to see and follow the flow of the program
*/

/* main code block */
//see if the form is submitted
if (!$_POST['submit']){
show_form();
}else{
//form submitted and need to get record(s)
get_data();
}//end if

//end main code block

function show_form()
{
//assume that city and category come from database


mysql_connect("localhost", "#", "#") or die(mysql_error());
mysql_select_db("BUSLIST") or die(mysql_error());


//get data for city and category
$sql = 'SELECT DISTINCT city FROM bus_list';
$r = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_object($r))
{
$city[] = $row->city;
}

$sql = 'SELECT DISTINCT category FROM bus_list';
$r = mysql_query($sql) or die(mysql_error());
while($row = mysql_fetch_object($r))
{
$category[] = $row->category;
}

$result_city = mysql_query($sql) or die ("Can't connect to perform query one because ".mysql_error());

$result_category = mysql_query($sql) or die ("Can't connect to perform query two because ".mysql_error());

if ((!result_city)||(!$result_category)){
echo "error retrieving data";
}else{
//place the results into an array
while ($rows=mysql_fetch_array($result_city )){
$city[]=$rows['city'];
}

//while ($rows1=mysql_fetch_array($result_category)){
//$category[]=$rows1['category'];
//}


//show the form headers
echo "
<body>
<form action="" . $_SERVER['PHP_SELF'] . "" method="post">
<table>
<tr>
<td>Select city</td></td>
<td>Select category</td>
</tr>
<tr>
<td><select name="city">";

//show the city data
for ($x=0;$x<count($city);$x++){
echo "<option value="".$city[$x]."">".$city[$x];
}
echo "</select></td><td>
<select name="category">";

//add the category data
for ($x=0;$x<count($category);$x++){
echo "<option value="".$category[$x]."">".$category[$x];
}
echo "</select></td></tr></table>
<input type="submit" name="submit" value="search">
</form></body>";
}
}//end function show_form

function get_data()
{
//get data from form
$city=$_POST['city'];
$category=$_POST['category'];

$sql="select * from bus_list where city='$city' and category='$category'";

mysql_connect("localhost", "#", "#") or die(mysql_error());
mysql_select_db("BUSLIST") or die(mysql_error());

$result=mysql_query($sql) or die ("can;t query because ".mysql_error());

echo "<table>"

while ($row=mysql_fetch_array($result){
echo"<tr><td>".$row['name']."</td><td>".$row['address']."</td><td>".$row['city']."</td><td>" ;//etc...etc


}
echo "</table>"

//show results to user
}
//end function get_data

?>
</body>

</html>
Reply With Quote
  #9  
Old August 15th, 2003, 02:05 PM
Blindeddie Blindeddie is offline
Codewalkers Regular (2000 - 2499 posts)
  
Join Date: Apr 2007
Location: NJ - USA
Posts: 2,152 Blindeddie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 4
RE: need help getting data to display
my bad... You need to put semi-colons after the 'echo "<table">' statements that come before and after the while statement (lines 108 and 115) That should fix it.
Reply With Quote
  #10