[SOLVED] Newbie help

[Perp]

OK, I am at the end of my wits here.  I am attempting to learn some PHP and SQL so that I can rework my web catalog to be able to update pricing easily.  I may very well be coming here a lot to get advice, but first I have to get past the first few problems I have encountered.

 

ok, I set up my server (win2kserver, iis5, php5, mysql).  I set up my database (2 fields of data, first field 'product' is all unique varchar(18), second field 'price' decimal(8.2)), and I filled it with a csv file (~9300 lines of data). 

 

I have successfully coded the mysql_connect and mysql_select_db with no problems.

 

The table is called 'prices', the fields are 'product' and 'price'.  My select statement is as follows:

$sql    = "SELECT price FROM prices WHERE product = 'PGPADJ'";
$result = mysql_query($sql, $link) or die(mysql_error());

if I use the following:

while($row = mysql_fetch_assoc($result)) {
       $price = $row['price'];
   }
   echo $price;

I get the result I want.  My first question is:  Do you have to use a while statement since this is a unique piece of data and there will NEVER be more than one result?  Is there a different way to get the query returned to the variable $price?

 

My second question is, how can I make $price = "CALL FOR PRICING" if the product is not found?  I tried the following after the query, but if I choose a product that does not exist, I get nothing, no error, no nothing (very frustrating):

if (!$result) {
     echo "Call for Pricing";
       exit;
   }

I figured that if this worked, then I could substitute the echo for $price = .  However, since I get nothing, I am at a loss. 

 

The next challenge is changing the item I am searching for in the select statement to a predefined variable.  I tried:

$product = 'PGPADJ';
$sql    = "SELECT price FROM prices WHERE product = $product";
$result = mysql_query($sql, $link) or die(mysql_error());

   while($row = mysql_fetch_assoc($result)) {
       $price = $row['price'];
   }
   echo $price;

but I get NOTHING again.  Is there a way to do this that works?

 

If I can get that working, then I would like to be able to call this up as a function, or execute the script my existing HTML pages.  There are over 500 pages for my catalog, with 30-100 lines of a table per page, so I want to be able to maybe call a function like:

pricefind('PGPADJ');

and have it return the price from the database for a PGPADJ part number.  Can I put the query code in a function?  I tried but get nothing, so I am at a loss here as well.

 

I assume I can do an include command and then somehow either bring in my price as a variable or as a function.  I need to get over the above challenges first however.

 

Any help at all here is greatly appreciated.

[suttercain]

To answer your first question. If you are only trying to receive one row from your mysql database, see:

http://us2.php.net/manual/en/function.mysql-fetch-row.php

 

$row = mysql_fetch_row($result);

 

Second question, try

if (mysql_num_rows($result) < 0) {
echo "Call for Pricing";
}

 

Third question,

$sql    = "SELECT price FROM prices WHERE product = $product";

 

Should be

$sql    = "SELECT price FROM prices WHERE product = '" .$product. "'";

 

 

[trq]

To try and answer all 4 questions...

 

<?php

  function pricefind($str) {
    // connect to db
    $sql    = "SELECT price FROM prices WHERE product = '$search' LIMIT 1";
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {
        $row = mysql_fetch_assoc($result)) {
        return $row['price'];
      }
    } else {
      return 'CALL FOR PRICING';
    }
  }

  // then, call the function using.
  echo pricefind('PGPADJ');

?>

[Perp]

To answer your first question. If you are only trying to receive one row from your mysql database, see:

http://us2.php.net/manual/en/function.mysql-fetch-row.php

 

$row = mysql_fetch_row($result);

If I do this, $row echoes as 'Array' and if I echo $row['prices'] I get nothing. ?

 

Second question, try

if (mysql_num_rows($result) < 0) {
echo "Call for Pricing";
}

excellent!  works, only I had to change the < to a <= since there wasnt a negative qty of rows..  thanks!

Third question,

$sql    = "SELECT price FROM prices WHERE product = $product";

 

Should be

$sql    = "SELECT price FROM prices WHERE product = '" .$product. "'";

sweet.. works as well.. thanks a million.

 

The first one is not such a big deal, but I am really curious why I cant get it to work.

[Perp]

To try and answer all 4 questions...

 

<?php

  function pricefind($str) {
    // connect to db
    $sql    = "SELECT price FROM prices WHERE product = '$search' LIMIT 1";
    if ($result = mysql_query($sql)) {
      if (mysql_num_rows($result)) {
        $row = mysql_fetch_assoc($result)) {
        return $row['price'];
      }
    } else {
      return 'CALL FOR PRICING';
    }
  }

  // then, call the function using.
  echo pricefind('PGPADJ');

?>

I tried this, but so far no luck.. couple questions:  Do I have to put all the connect script in the function or can I have that before the function?  Reason I ask that one is because I will have to repeat the function many times in each page.  Also, should I free $result in the function as well so that when I call it again it is blank? 

 

Is it normal to get nothing when testing a script that has something wrong?  This is frustrating.. I cant tell where my errors are.. hehe.

[suttercain]

If I do this, $row echoes as 'Array' and if I echo $row['prices'] I get nothing. ?

 

Try echo $row[0];

 

Keep me posted

[Perp]

OK, I played around a little, and got it to work.. My big concern now is if I repeat this function say 100 times in a page is it going to be incredibly slow?  Is there a better way to do it?

Here is the php code:

<?php
function price($product) {
if (!$link = mysql_connect('localhost:3306', 'root', 'password')) {
    echo 'Could not connect to mysql';
    exit;
}
if (!mysql_select_db('data', $link)) {
    echo 'Could not select database';
    exit;
}
$sql    = "SELECT price FROM prices WHERE product = '" .$product. "'";
$result = mysql_query($sql, $link) or die(mysql_error());

// the following returns 'CALL' if product is not in the database
   if (mysql_num_rows($result) <= 0) {
    echo "CALL";
    }

   $row = mysql_fetch_row($result);
   echo $row[0];
    
   mysql_free_result($result);

   mysql_close($link);
}
?>

so I put that at the beginning of a page, and where I have a price in my static HTML code, I replace it with:

<?PHP echo price('PGPADJ')?>

and it works.. However, this is opening the connection, opening the database, selecting the price from the data, printing the result, and then closing the connection.  This might happen 100 times in a given page.. Right now its all on my local test server, but when I start putting code into my live pages, it will have to do this where the data is on a different server.. I fear it might be very slow..

 

Does anyone know if there is a way to do this where the database is opened at the beginning of the page, then the function is run a bunch of times, then the connection closed at the end?  If I put the connect scripts before the function, it doesnt work.. do I have to use a session or something? 

 

suttercain: thanks that worked great!  I appreciate the help!

[trq]

Leave $link out of your call to mysql_connect(). mysql_connect will automatically use the first available connection. This way, you can open the connection at the top of your script and call the function anywhere without needing to open a connection within the function.

 

Also... if your going to echo your result within the function itself you only need call it with....

 

<?php price('PGPADJ'); ?>

[Perp]

Thanks!  Thorpe, that worked great.. I had added another database where all I did was store the date I updated the pricing, so I could have a line at the beginning of each pricing sheet showing the date the pricing was updated, and it took me a little bit to figure it out since I had the 2 functions (the pricing function and the dating function) all at the top of my page, each with its own connection scripts.. I just moved the php script for the date to its own block at the top of the page, then did the heading info with the date below that, then I did the db opening scripts and the pricing function, then the html, then closed the connection at the end.  It works great and now I am not opening and closing the db connection 50 or 100 times in a page..

 

I greatly appreciate all the help, even if it seems like my problems were trivial.  I am a novice at HTML code, and I didn't know the first thing about PHP or SQL until Monday morning. After 10 hours a day of surfing the internet for information, buying 2 big books on the subjects, setting up my test server, grinding out the code I needed, and coming here to figure out what I did wrong, I finally implemented it live in the application I was looking for.  Not only did I accomplish my goal, I have learned a great deal about web design, php scripting, sql databases, and my webserver.  To think I was going to hire someone to change my catalog to a database driven system.. Now all I need is about 200 hours with nobody bothering me and I can update the 350+ pages of html with this new code.  haha.

 

I will keep this site bookmarked for future reference.. This PHP opens up some new possibilities for my website.. now I just have to find the time to play with it.  Thanks again to everyone that helped!