Having trouble connecting to my first MySQL database

  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

On my flicksticks.com web site, I have decided I want to have a random quote at the bottom of each page. I figured this would be a good way to get my feet wet in the world of MySQL, and so I decided to set up a DB and table and access via PHP.

I am now getting the dreaded "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource" error on a mysql_fetch_array statement. The code is included below. Basically at this point I'm just trying access the database and get things working before actually diving into the actual code I'll need, so I'm trying to print out all of the QUOTEID fields in the table, which are integers.

Don't think this will help, but the page I am testing this on is here. I have run the SELECT statement against my DB in the control panel with favorable results.

Code: [ Select ]
$db = mysql_connect("localhost", "username", "pwd") or die("could not connect");
mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
$sql = "SELECT QUOTEID FROM quotes WHERE QUOTEID > 0";
echo $sql;
$result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());

while ($row = mysql_fetch_array($sql_result)) {
    printf("QUOTEID: %c<br>\n", $row["QUOTEID"]); 
}
  1. $db = mysql_connect("localhost", "username", "pwd") or die("could not connect");
  2. mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
  3. $sql = "SELECT QUOTEID FROM quotes WHERE QUOTEID > 0";
  4. echo $sql;
  5. $result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
  6. while ($row = mysql_fetch_array($sql_result)) {
  7.     printf("QUOTEID: %c<br>\n", $row["QUOTEID"]); 
  8. }


Thanks for your help, Ozzu!

//I edited out your username and password from your code. That shouldn't be made public -- ATNO
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • this213
  • Guru
  • Guru
  • User avatar
  • Posts: 1260
  • Loc: ./

Post 3+ Months Ago

Code: [ Select ]
$result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());

while ($row = mysql_fetch_array($sql_result)) {
  1. $result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
  2. while ($row = mysql_fetch_array($sql_result)) {

you need to either change the first line here to
$sql_result = mysql_query....
or change the while to:
while ($row = mysql_fetch_array($result)) {
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

this213 wrote:
Code: [ Select ]
$result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());

while ($row = mysql_fetch_array($sql_result)) {
  1. $result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
  2. while ($row = mysql_fetch_array($sql_result)) {

you need to either change the first line here to
$sql_result = mysql_query....
or change the while to:
while ($row = mysql_fetch_array($result)) {



HAHAHAHA! You have got to be kidding me!

Thanks a ton, this. You know the way it is. I'm kind of glad I posted rather than sitting up all night piddling around this stupid mistake. Thanks again!
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

ATNO wrote:
//I edited out your username and password from your code. That shouldn't be made public -- ATNO


Thanks, ATNO. Speaking of that...

I set up the user as SELECT only because I was concerned about security. Now if I want to do some row INSERTs and DELETEs, is it secure enough to simply throw the password into each page? It kind of scares me as you'd think there'd be a way for someone out there to get at the page before the PHP is processed.
  • Mr. Wiggles
  • Graduate
  • Graduate
  • Mr. Wiggles
  • Posts: 160
  • Loc: Brizzle

Post 3+ Months Ago

You can either leave the passwords in the page, and nobody can access them unless you make an error in your code which displays them or they have access to that particular file on ftp.
You can also have a seperate .inc file and have that saved on your server with $user $password $host variables. You can then use the include function and the $db variable should look like this.
PHP Code: [ Select ]
 
$db = mysql_connect("$host", "$user", "$password") or die("could not connect");
 
 
  1.  
  2. $db = mysql_connect("$host", "$user", "$password") or die("could not connect");
  3.  
  4.  

But people can access hte .inc using their browser, so I think you need to encode that apge somehow. Either way, you should be secure enough.
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

Thanks for the reply. Seems like at this point, unless there are more opinions, I might as well just stick with the password on the page.

The random quotes are working and at the bottom of every page. Check the flicksticks.com link in my sig if you are interested in seeing it in action.
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

What you could do to prevent your username and p-word being in every page is to create a function that opens the db in a seperate file and just include that file and call the function.

That is a good point though. What call does MySQL use to encrypt the password is it MD5 or PASWORD?
  • rtm223
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1855
  • Loc: Uk

Post 3+ Months Ago

Mr. Wiggles wrote:
But people can access hte .inc using their browser, so I think you need to encode that apge somehow. Either way, you should be secure enough.


Make the .inc a .php file. In theory, if the file is a php, and you DO NOT allow anonymous ftp, itis not possible for people to get access to the php source code.

As for silly mistakes, I have been using this:
PHP Code: [ Select ]
 
/* ~~~~~~~~~~~~~~~~~~~( Error Reporting Level ) ~~~~~~~~~~~~~~~~~~~
 
for test purposes: */
 
   error_reporting(2047);
 
//Production Environment:
 
   error_reporting(0);
 
 
  1.  
  2. /* ~~~~~~~~~~~~~~~~~~~( Error Reporting Level ) ~~~~~~~~~~~~~~~~~~~
  3.  
  4. for test purposes: */
  5.  
  6.    error_reporting(2047);
  7.  
  8. //Production Environment:
  9.  
  10.    error_reporting(0);
  11.  
  12.  

And just commenting out the appropriate line. If you had been using 2047 for the error reporting it would have warned you of the uninitiated variable.
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

rtm223 wrote:

Make the .inc a .php file. In theory, if the file is a php, and you DO NOT allow anonymous ftp, itis not possible for people to get access to the php source code.


So if the code containing the userid/password is an included PHP file, then is there really any reason to make another level of included files?


rtm223 wrote:
As for silly mistakes, I have been using this:
PHP Code: [ Select ]
/* ~~~~~~~~~~~~~~~~~~~( Error Reporting Level ) ~~~~~~~~~~~~~~~~~~~
for test purposes: */
   error_reporting(2047);
//Production Environment:
   error_reporting(0);
 
  1. /* ~~~~~~~~~~~~~~~~~~~( Error Reporting Level ) ~~~~~~~~~~~~~~~~~~~
  2. for test purposes: */
  3.    error_reporting(2047);
  4. //Production Environment:
  5.    error_reporting(0);
  6.  

And just commenting out the appropriate line. If you had been using 2047 for the error reporting it would have warned you of the uninitiated variable.


Very cool. I'm just getting into this PHP mess, and it is quite fun. Learning a lot!

My next step will probably be converting the whole site to dynamic page requests. The only problem with that is probably the unique Amazon ads on each movie page, but I'm sure something could be done for that.
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

You know, I might as well run the random code by you guys to see how inefficient you may think it is. :) Thanks for any more help you can give!

PHP Code: [ Select ]
$db = mysql_connect("localhost", "userid", "password") or die("could not connect");
 
mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
 
$sql = "SELECT * FROM quotes";
 
$result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
 
$arrayCount = 0;
 
     
 
while ($row = mysql_fetch_array($result)) {
 
   $arrayCount++;
 
}
 
     
 
srand((double)microtime()*1000000);
 
$randomNumber  = rand(0,$arrayCount-1);
 
     
 
print("<p>\n");
 
printf("\"%s\" ~%s\n", mysql_result($result,$randomNumber,"QUOTE"), mysql_result($result,$randomNumber,"AUTHOR"));
 
print("</p>\n");
  1. $db = mysql_connect("localhost", "userid", "password") or die("could not connect");
  2.  
  3. mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
  4.  
  5. $sql = "SELECT * FROM quotes";
  6.  
  7. $result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
  8.  
  9. $arrayCount = 0;
  10.  
  11.      
  12.  
  13. while ($row = mysql_fetch_array($result)) {
  14.  
  15.    $arrayCount++;
  16.  
  17. }
  18.  
  19.      
  20.  
  21. srand((double)microtime()*1000000);
  22.  
  23. $randomNumber  = rand(0,$arrayCount-1);
  24.  
  25.      
  26.  
  27. print("<p>\n");
  28.  
  29. printf("\"%s\" ~%s\n", mysql_result($result,$randomNumber,"QUOTE"), mysql_result($result,$randomNumber,"AUTHOR"));
  30.  
  31. print("</p>\n");
  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

This is the way I usually deal with error reporting

PHP Code: [ Select ]
 
$err_level = 2047; // or any other accepted value 0 being none
 
error_reporting($err_level);
 
 
  1.  
  2. $err_level = 2047; // or any other accepted value 0 being none
  3.  
  4. error_reporting($err_level);
  5.  
  6.  


Just to throw something else in the mix :wink:
  • ScienceOfSpock
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1893
  • Loc: Las Vegas

Post 3+ Months Ago

This should work better for you, as it reduces the amount of rows returned to 1 and allocates the randomness to mysql's build in rand() function:
PHP Code: [ Select ]
 
$db = mysql_connect("localhost", "userid", "password") or die("could not connect");
 
mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
 
$sql = "SELECT * FROM quotes order by rand() limit 1";
 
$result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
 
         
 
print("<p>\n");
 
printf("\"%s\" ~%s\n", mysql_result($result,0,"QUOTE"), mysql_result($result,0,"AUTHOR"));
 
print("</p>\n");
 
 
  1.  
  2. $db = mysql_connect("localhost", "userid", "password") or die("could not connect");
  3.  
  4. mysql_select_db("bjhessfl_quotes") or die("Couldn't select the database");
  5.  
  6. $sql = "SELECT * FROM quotes order by rand() limit 1";
  7.  
  8. $result = mysql_query($sql) or die ('Query error:<br />' .mysql_error());
  9.  
  10.          
  11.  
  12. print("<p>\n");
  13.  
  14. printf("\"%s\" ~%s\n", mysql_result($result,0,"QUOTE"), mysql_result($result,0,"AUTHOR"));
  15.  
  16. print("</p>\n");
  17.  
  18.  


note: this is untested, but should do what you want in fewer lines of code.
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

Thanks, ScienceOfSpock. I'm always blown away by what SQL can do for you. Works like a charm.
  • ScienceOfSpock
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1893
  • Loc: Las Vegas

Post 3+ Months Ago

bjhess wrote:
Thanks, ScienceOfSpock. I'm always blown away by what SQL can do for you. Works like a charm.


No problem. I'm often blown away by it's power as well :)
  • bjhess
  • Student
  • Student
  • User avatar
  • Posts: 77
  • Loc: Owatonna, Minnesota

Post 3+ Months Ago

flicksticks.com

If I can convince you to look through some more of my code as a form of review, that'd be outstanding.

First, I've made my whole site dynamic, but the bulk of the 'cool stuff' is on the "flicks" pages. I've created a table for these containing a short name (for page queries and GIF file naming), long name (for listing on menus and page headers), number of graphics (since I'll use the same code to generate each Flick graphic, and the Amazon adsense code.

Here's how I spit out the content portion of the page:

PHP Code: [ Select ]
$rows = 0;
 
 
 
//first, I'm going to connect to the DB if a page is defined in the URL
 
//e.g. - <!-- w --><span class="postlink">http://www.flicksticks.com/index.php?page=chicago</span><!-- w -->
 
if ($page) {
 
   $db = mysql_connect("localhost", "username", "pass") or die("<br />Could not connect");
 
   mysql_select_db("bjhessfl_quotes") or die("<br />Couldn't select the database");
 
   $flickPagesSql = "SELECT * FROM flickPages WHERE shortTitle = \"" . $page . "\"";
 
   $flicksPagesResult = mysql_query($flickPagesSql) or die ('<br />Query error:<br />' .mysql_error());
 
   $rows = mysql_num_rows($flicksPagesResult);
 
}
 
     
 
if ($rows == 0 ) {
 
   //unfortunately I haven't committed every page to mySQL
 
   switch ($page) {
 
      case "jenniejiles":
 
         include('sticks/jenniejiles.php');
 
         break;
 
      case "stephenmcsaint":
 
         include('sticks/stephenmcsaint.php');
 
         break;
 
      case "emailform":
 
         include('emailform.php');
 
         break;
 
      default:
 
         include('main.php');
 
   }
 
} else {
 
   $title = mysql_result($flicksPagesResult,0,"title");
 
   $shortTitle = mysql_result($flicksPagesResult,0,"shortTitle");
 
   $numGraphics = mysql_result($flicksPagesResult,0,"numGraphics");
 
   //amazonAd will be used later to solve my dynamic ad problems for each Flick
 
   $amazonAd = mysql_result($flicksPagesResult,0,"amazonAd");
 
   
 
   print "<h1>" . $title . "</h1>\r\n\r\n";
 
   
 
   //my graphic naming standard is shortTitle##.jpg
 
   //e.g. - sayanything01.jpg, sayanything02.jpg, etc
 
   //this will loop based on the number of graphics I
 
   //designated in the DB and should be able to handle
 
   //up to 99 graphics.
 
   for ($ii = 1; $ii <= $numGraphics; $ii++) {
 
      $output = "<img src=\"/flicks/graphics/";
 
      $output .= ($ii < 10) ? $shortTitle . "0" . $ii : $shortTitle . $ii;
 
      $output .= ".gif\" alt=\"" . $title . "\">\r\n\r\n";
 
      print $output;
 
   }
 
   
 
   //HaloScan page comments included below
 
   include 'scripts/pagecomments.php';
 
}    
 
 
 
 
  1. $rows = 0;
  2.  
  3.  
  4.  
  5. //first, I'm going to connect to the DB if a page is defined in the URL
  6.  
  7. //e.g. - <!-- w --><span class="postlink">http://www.flicksticks.com/index.php?page=chicago</span><!-- w -->
  8.  
  9. if ($page) {
  10.  
  11.    $db = mysql_connect("localhost", "username", "pass") or die("<br />Could not connect");
  12.  
  13.    mysql_select_db("bjhessfl_quotes") or die("<br />Couldn't select the database");
  14.  
  15.    $flickPagesSql = "SELECT * FROM flickPages WHERE shortTitle = \"" . $page . "\"";
  16.  
  17.    $flicksPagesResult = mysql_query($flickPagesSql) or die ('<br />Query error:<br />' .mysql_error());
  18.  
  19.    $rows = mysql_num_rows($flicksPagesResult);
  20.  
  21. }
  22.  
  23.      
  24.  
  25. if ($rows == 0 ) {
  26.  
  27.    //unfortunately I haven't committed every page to mySQL
  28.  
  29.    switch ($page) {
  30.  
  31.       case "jenniejiles":
  32.  
  33.          include('sticks/jenniejiles.php');
  34.  
  35.          break;
  36.  
  37.       case "stephenmcsaint":
  38.  
  39.          include('sticks/stephenmcsaint.php');
  40.  
  41.          break;
  42.  
  43.       case "emailform":
  44.  
  45.          include('emailform.php');
  46.  
  47.          break;
  48.  
  49.       default:
  50.  
  51.          include('main.php');
  52.  
  53.    }
  54.  
  55. } else {
  56.  
  57.    $title = mysql_result($flicksPagesResult,0,"title");
  58.  
  59.    $shortTitle = mysql_result($flicksPagesResult,0,"shortTitle");
  60.  
  61.    $numGraphics = mysql_result($flicksPagesResult,0,"numGraphics");
  62.  
  63.    //amazonAd will be used later to solve my dynamic ad problems for each Flick
  64.  
  65.    $amazonAd = mysql_result($flicksPagesResult,0,"amazonAd");
  66.  
  67.    
  68.  
  69.    print "<h1>" . $title . "</h1>\r\n\r\n";
  70.  
  71.    
  72.  
  73.    //my graphic naming standard is shortTitle##.jpg
  74.  
  75.    //e.g. - sayanything01.jpg, sayanything02.jpg, etc
  76.  
  77.    //this will loop based on the number of graphics I
  78.  
  79.    //designated in the DB and should be able to handle
  80.  
  81.    //up to 99 graphics.
  82.  
  83.    for ($ii = 1; $ii <= $numGraphics; $ii++) {
  84.  
  85.       $output = "<img src=\"/flicks/graphics/";
  86.  
  87.       $output .= ($ii < 10) ? $shortTitle . "0" . $ii : $shortTitle . $ii;
  88.  
  89.       $output .= ".gif\" alt=\"" . $title . "\">\r\n\r\n";
  90.  
  91.       print $output;
  92.  
  93.    }
  94.  
  95.    
  96.  
  97.    //HaloScan page comments included below
  98.  
  99.    include 'scripts/pagecomments.php';
  100.  
  101. }    
  102.  
  103.  
  104.  
  105.  



Included HaloScan page comments:

Code: [ Select ]
<div id="comment">
<br/>
<br/>
<a href="javascript:HaloScan('<?php print "$shortTitle"; ?>');" target="_self"><script type="text/javascript">postCount('<?php print "$shortTitle"; ?>');</script></a>
</div>
  1. <div id="comment">
  2. <br/>
  3. <br/>
  4. <a href="javascript:HaloScan('<?php print "$shortTitle"; ?>');" target="_self"><script type="text/javascript">postCount('<?php print "$shortTitle"; ?>');</script></a>
  5. </div>



After creating this, I figured I might as well make the Flicks menu dynamic as well:

PHP Code: [ Select ]
$db = mysql_connect("localhost", "username", "pass") or die("<br />Could not connect");
 
mysql_select_db("bjhessfl_quotes") or die("<br />Couldn't select the database");
 
$flickMenuSql = "SELECT shortTitle, title FROM flickPages ORDER BY shortTitle";
 
$flicksMenuResult = mysql_query($flickMenuSql) or die ('<br />Query error:<br />' .mysql_error());
 
$rows = mysql_num_rows($flicksMenuResult);
 
         
 
for ($ii = 0; $ii < $rows; $ii++) {
 
   $title = mysql_result($flicksMenuResult,$ii,"title");
 
   $shortTitle = mysql_result($flicksMenuResult,$ii,"shortTitle");
 
   print "<a href=\"/index.php?page=" . $shortTitle . "\">" . $title . "</a><br />\r\n";
 
}
  1. $db = mysql_connect("localhost", "username", "pass") or die("<br />Could not connect");
  2.  
  3. mysql_select_db("bjhessfl_quotes") or die("<br />Couldn't select the database");
  4.  
  5. $flickMenuSql = "SELECT shortTitle, title FROM flickPages ORDER BY shortTitle";
  6.  
  7. $flicksMenuResult = mysql_query($flickMenuSql) or die ('<br />Query error:<br />' .mysql_error());
  8.  
  9. $rows = mysql_num_rows($flicksMenuResult);
  10.  
  11.          
  12.  
  13. for ($ii = 0; $ii < $rows; $ii++) {
  14.  
  15.    $title = mysql_result($flicksMenuResult,$ii,"title");
  16.  
  17.    $shortTitle = mysql_result($flicksMenuResult,$ii,"shortTitle");
  18.  
  19.    print "<a href=\"/index.php?page=" . $shortTitle . "\">" . $title . "</a><br />\r\n";
  20.  
  21. }



I guess one of my first thoughts would be that I should be able to share these DB connections. With the random comment thing I talked about in the origination of this thread, each "page" will be making a DB connection three times. Probably not good. Unfortunately I had problems figuring out how to do this. Is it possible?

My next question is, should I go so far as to store the "Sticks" pages' content in a database? Is there really a point if I'm only going to be adding a couple more items there?

I can see that if my number of Flicks gets out-of-hand, I should be able to add an Archive boolean to the table in order to only list what I want on the main menu. Very convenient.

It also seems that creating a Random Flick option would be cake now.

I really can see the advantages of making a web site dynamic - I just know I'm not doing it completely right. :) Sorry for the long post, I won't feel too bad if no one wants to bother.

Post Information

  • Total Posts in this topic: 15 posts
  • Users browsing this forum: No registered users and 123 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.