Need help with MYSQL connection & displaying output

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Need help with MYSQL connection & displaying output

Post by prototype18 »

Code: Select all

mysql_select_db("liecon63_bizdir" , $db) or die("Couldn't open database: ".mysql_error());
$sqlquery = "SELECT * FROM liecon63_bizdir WHERE (bizcategory = '".$category."')";
$biznm = trim($row['bizname']);
$sqlresult = mysql_query($sqlquery);
$sqlmyrow = mysql_fetch_array($sqlresult, MYSQL_ASSOC);
Thats the code I have right now. I'm trying to build a PHP business directory that will fit into my site. I am having problems displaying output from the db.

I get a "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/liecon63/public_html/bizdir/bizdisplay3.php on line 62"

Please help!
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Need help with MYSQL connection & displaying output

Post by mikosiko »

your code is incomplete... is not showing per example: where are you making a db connection, is not showing from where the variable $category is taken it value... in the code that you are showing you are not controlling errors.. per example:

you should at least write your code like this:

Code: Select all

<?php

  // Set the error(s) reporting level
   error_reporting(E_ALL); 
   ini_set("display_errors", 1);
   
   // Open a connection link to your Database Engine
   $link = mysql_connect('yourhost', 'yourusername', 'yourpassword') or die("Connection Error : " . mysql_error());

   // Select the database to work with
   mysql_select_db('yourdatabase') or die("Database Selection Error : " . mysql_error());

   // Define your query
   $sqlquery = "SELECT * FROM liecon63_bizdir WHERE bizcategory = '".$category."'"; //assuming that you have defined $category variable before in some way

   // Execute your query (returning a result set in this case
   $sqlresult = mysql_query($sqlquery) or die("Query Error : " . $sqlquery . "<br /> Error: " . mysql_error());

   // Loop through your result set to process the results
   while ($row = mysql_fetch_array($sqlresult, MYSQL_ASSOC)  // here you can simply use mysql_fetch_assoc() instead
   {
        // Process your records here... like
       echo "BizName = " . $row['bizname'] . "<br />";
       //  etc.. etc..
   }

// Close your Db Engine Link
mysql_close($link)
?>
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

Code: Select all

<?php

  // Set the error(s) reporting level
   error_reporting(E_ALL);
   ini_set("display_errors", 1);
   
   // Open a connection link to your Database Engine
   $link = mysql_connect('localhost', 'liecon63_bizdir', 'pass') or die("Connection Error : " . mysql_error());

   // Select the database to work with
   mysql_select_db('liecon63_bizdir') or die("Database Selection Error : " . mysql_error());

   // Define your query
   $sqlquery = "SELECT * FROM liecon63_bizdir WHERE bizcategory = '".$category."'"; //assuming that you have defined $category variable before in some way

   // Execute your query (returning a result set in this case
   $sqlresult = mysql_query($sqlquery) or die("Query Error : " . $sqlquery . "<br /> Error: " . mysql_error());

   // Loop through your result set to process the results
   while ($row = mysql_fetch_array($sqlresult, MYSQL_ASSOC)  // here you can simply use mysql_fetch_assoc() instead
   {
        // Process your records here... like
       echo "BizName = " . $row['bizname'] . "<br />";
       //  etc.. etc..
   }

// Close your Db Engine Link
mysql_close($link)
?>
Says there is a syntax error on line 25, unexpected {
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Need help with MYSQL connection & displaying output

Post by mikosiko »

prototype18 wrote:

Code: Select all

<?php
.....
// Close your Db Engine Link
mysql_close($link)
?>
Says there is a syntax error on line 25, unexpected {
and you did something to understand why?... :wink:
I missed a ) here

Code: Select all

 while ($row = mysql_fetch_array($sqlresult, MYSQL_ASSOC) )
and I missed a ; in this line

Code: Select all

mysql_close($link)
should be

Code: Select all

mysql_close($link);
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

THANK YOU! It was actually a missing ) on top of the ; at the end, but thanks! I got it to work now! :D
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

Code: Select all

<?php

  // Set the error(s) reporting level
   error_reporting(E_ALL);
   ini_set("display_errors", 1);
   $category = "Professional Services";
   
   // Open a connection link to your Database Engine
   $link = mysql_connect('localhost', 'liecon63_bizdir', 'pass') or die("Connection Error : " . mysql_error());

   // Select the database to work with
   mysql_select_db('liecon63_bizdir') or die("Database Selection Error : " . mysql_error());

   // Define your query
   $sqlquery = "SELECT * FROM bizdir WHERE bizcategory = '".$category."'"; //assuming that you have defined $category variable before in some way

   // Execute your query (returning a result set in this case
   $sqlresult = mysql_query($sqlquery) or die("Query Error : " . $sqlquery . "<br /> Error: " . mysql_error());

   // Loop through your result set to process the results
   while ($row = mysql_fetch_array($sqlresult, MYSQL_ASSOC))  // here you can simply use mysql_fetch_assoc() instead
   {
        // Process your records here... like
       echo "BizName = " . $row['bizname'] . "<br />";
	   echo "BizName = " . $row['bizaddress'] . "<br />";
	   echo "BizName = " . $row['bizphone'] . "<br />";
       //  etc.. etc..
   }

// Close your Db Engine Link
mysql_close($link);
?>
Now how would I go about displaying the output in an array to show all the records of certain categories?

I also need to create a search utility as well that will display records in HTML tables on a page.

You guys on here are awesome, and I really appreciate the help! :D
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

As I've explained before, I need to display the output of businesses in a table based on category and sub category.

Based on the code I have...

Code: Select all

<?php

  // Set the error(s) reporting level
   error_reporting(E_ALL);
   ini_set("display_errors", 1);
   $category = "Professional Services";
   
   // Open a connection link to your Database Engine
   $link = mysql_connect('localhost', 'liecon63_bizdir', 'pass') or die("Connection Error : " . mysql_error());

   // Select the database to work with
   mysql_select_db('liecon63_bizdir') or die("Database Selection Error : " . mysql_error());

   // Define your query
   $sqlquery = "SELECT * FROM bizdir WHERE bizcategory = '".$category."'"; //assuming that you have defined $category variable before in some way

   // Execute your query (returning a result set in this case
   $sqlresult = mysql_query($sqlquery) or die("Query Error : " . $sqlquery . "<br /> Error: " . mysql_error());

   // Loop through your result set to process the results
   while ($row = mysql_fetch_array($sqlresult, MYSQL_ASSOC))  // here you can simply use mysql_fetch_assoc() instead
   {
        // Process your records here... like
       echo "BizName = " . $row['bizname'] . "<br />";
	   echo "BizName = " . $row['bizaddress'] . "<br />";
	   echo "BizName = " . $row['bizphone'] . "<br />";
       //  etc.. etc..
   }
   ?>
   <table border="0" width="100%" id="table3">
				<tr>
					<td width="400">
<b><i><font face="Arial" size="3">Affordable Accountants </font><font face="Arial" size="2">
        <a target="_blank" href="http://maps.google.com/maps?q=367+Sunrise+Highway,+West+Babylon,+NY+11704&spn=0.026699,0.057811&hl=en">
        <font size="3">
        <img border="0" src="http://www.lieconomy.com/li/images/map.gif" width="46" height="19"></font></a></font><font face="Arial" size="3"></a>
</font>
</i>
</b><font face="Arial" size="3"><br>
    </font><font face="Arial" size="2">4910 Merrick Road Suite B60<br>
    Massapequa Park, New York 11762<br>
    </font><font face="Arial" size="3"><b>(516) 795-1223</b></font><font size="2"><b> </b></font></td>
</tr>
</table>
<?
mysql_close($link);
?>
I need help being able to output multiple records based on the criteria that I set above. Businesses should be shown based on category & sub category.'

If anyone could assist me in this task, it would be MORE THAN APPRECIATED :bow:
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

I would think it needs to be put into an array, because I have like 40 records for businesses in the db for the "Professional Services" category. How would I set this array up?
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

Code: Select all

	   $bizname = $row['bizname'];
	   $bizaddress = $row['bizaddress'];
	   $bizcity = $row['bizcity'];
	   $bizstate = $row['bizstate'];
	   $bizzip = $row['bizzip'];
	   $bizphone = $row['bizphone'];
	   $bizgooglemap = $row['bizgooglemap'];
	   
	   $params = array('bizname'=>$bizname,'bizaddress'=>$bizaddress,'bizcity'=>$bizcity,'bizstate'=>$bizstate,'bizzip'=>$bizzip,'bizphone'=>$bizphone);
$response = (array($params));
$response = $response;
    
//Cast the output as an array remove the procedure array
$response = (array)$response;
That is what I have so far for my array. Although there are no errors, it is not returning data in the array.
TonsOfFun
Forum Commoner
Posts: 54
Joined: Wed Jun 02, 2010 7:37 pm

Re: Need help with MYSQL connection & displaying output

Post by TonsOfFun »

prototype18 wrote:
That is what I have so far for my array. Although there are no errors, it is not returning data in the array.

You need to use a while{} loop.
Just google this and you'll find a ton of tutorials to help you.
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

What do I google? Just "while{} loop"?
stuartr
Forum Newbie
Posts: 13
Joined: Sun Sep 12, 2010 11:11 am

Re: Need help with MYSQL connection & displaying output

Post by stuartr »

I still find one of the best sites is the W3Schools site http://www.w3schools.com whether you are looking for introductions to html, css, php, mysql or many others.

Check the php section out - the particular section for while loops is here: http://www.w3schools.com/php/php_looping.asp
prototype18
Forum Commoner
Posts: 25
Joined: Wed Aug 18, 2010 9:52 am

Re: Need help with MYSQL connection & displaying output

Post by prototype18 »

Still not quite sure how to implement the while{} loop in my code... :(
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Need help with MYSQL connection & displaying output

Post by mikosiko »

is eluding me why you need an array ... if you only want to display the records that your select is retrieving in a html table why don't do that in the same while loop that you code has now? or your want to store those records in an array to process them later in another way?... your objectives are not clear for me
stuartr
Forum Newbie
Posts: 13
Joined: Sun Sep 12, 2010 11:11 am

Re: Need help with MYSQL connection & displaying output

Post by stuartr »

The code below shows the use of a do..while loop that displays a row in a table for each record retrieved in a query.

Retrieving database records from mysql

Code: Select all

$query_rs_getBooks = sprintf("SELECT books.book_id, books.title, books.author_surname, books.author_firstName FROM books ORDER BY books.author_surname, books.author_firstName");
$rs_getBooks = mysql_query($query_rs_getBooks, $conn1) or die(mysql_error());
$row_rs_getBooks = mysql_fetch_assoc($rs_getBooks);
Looping through the records with a do...while loop to display each record in a table row.

Code: Select all

    <?php do { ?>
      <tr>
        <td><?php echo $row_rs_getBooks['title']; ?></td>
        <td><?php echo $row_rs_getBooks['author_firstName']; ?> <?php echo $row_rs_getBooks['author_surname']; ?></td>
        <td><?php echo $row_rs_getBooks['review_date']; ?></td>
      </tr>
      <?php } while ($row_rs_getBooks = mysql_fetch_assoc($rs_getBooks)); ?>
Hopefully this will give you a start in understanding how do/while loops work, its worth the pain as once you understand loops you will find php much easier to work with.
Post Reply