SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

    Hello,

    I receive the following error message when running the script below:

    Any help would be very much apprechiated.

    Many Thanks

    Richard

    PHP Code:
    <?php require_once('../Connections/pricing.php'); ?>
    <?php 

    $result 
    mysql_query ("SELECT pack_price,pack_setup FROM package_type WHERE pack_id=21");

    list(
    $pack_price,$pack_setup) = mysql_fetch_array($result);

    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    <body>
    <?php echo $result ?>
    </body>
    </html>

  2. #2
    SitePoint Addict fesh's Avatar
    Join Date
    Jan 2005
    Location
    Jackson, New Jersey
    Posts
    218
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what is the database connection? your result variable isn't techincally pulling from anywhere.
    Fesh
    imagine what it's going to be like

  3. #3
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    <?php
    $dbhost = 'localhost';
    $dbuser = 'REMOVED';
    $dbpass = 'REMOVED';

    $conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

    $dbname = 'REMOVED';
    mysql_select_db($dbname);
    ?>

  4. #4
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is now displaying

    Resource id #4

    However it is meant to be displaying

    1.99|1.66333333333|1.665|1.66583333333|0|0

    Ideally i would like the ability for it to just display the first item or any of my choice for that fact.

    Regards

    Richard

  5. #5
    SitePoint Addict
    Join Date
    Sep 2002
    Posts
    225
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason why it's not working correctly is that you are echoing $result in your html. You need to be echoing $pack_price,$pack_setup

  6. #6
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Many Thanks, it is working now. Do you know how i would go about only displaying only one of the variables for pack_price as it is currently displaying the whole field 1.99|1.66333333333|1.665|1.66583333333|0|0

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you will always have to "string" and "unstring" those values, and so will the database

    this is not even in first normal form, and it is not a very good way to design a database table

    those values should be in separate fields

    but in the meantime, you can use the SUBSTRING_INDEX function to pull out the value that you want
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    this is not even in first normal form, and it is not a very good way to design a database table
    The database was not designed by me and some software that we purchased is running off it. How would i go about implementing the the SUBSTRING_INDEX function into the following:

    Many Thanks

    Richard

    PHP Code:
    <?php require_once('../Connections/pricing.php'); ?>
    <?php 

    $result 
    mysql_query ("SELECT pack_price,pack_setup FROM package_type WHERE pack_id=21 ");

    list(
    $pack_price,$pack_setup) = mysql_fetch_array($result);

    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    <body>
    <?php echo $pack_price,$pack_setup ?>
    </body>
    </html>

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    have you checked the manual? it's really very simple
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The problem that i can see is if i change the price in our billing software this will then change the string in the database meaning that it wont display on the website. Am i correct?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    will it display on the website? i have no idea
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason that i ask is that according to http://uk.php.net/substr() you have to specify what the value will contain before you can pull it out of the database.

    The problem is that the prices are stored in the database like this:

    Package 1 1.99|1.66333333333|1.665|1.66583333333|0|0

    Package 2 7.99|3.66333333333|2.665|75.66583333333|0|0

    Is there anyway i can pull out value 1 and display it without having to specify what it will contain first.

    Regards,

    Richard

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i strongly advise you to change that design -- you are asking for a world of hurt and the application will be as slow as cold glue

    meanwhile, you can use the SUBSTRING_INDEX functoin in mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    As much as i would like to change this it is not possible. The software was purchased and is encoded which makes this impossible. Therefore my only option is to stick with it this way. How would i go about extracting one value from the database using the SUBSTRING_INDEX function in mysql? An example would be very much apprechiated.

    Many Thanks,

    Richard

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by richard1
    The software was purchased and is encoded which makes this impossible.
    then you should shoot the idiot who selected this piece o' cra...

    oh, wait, maybe that was you

    okay, with two suggestions that you use the SUBSTRING_INDEX function, you still need an example, so here it is...
    Code:
    select substring_index(
           substring_index(pack_price,'|',2)
                          ,'|',-1) 
              as second_price
      from ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry i am very new to this how could i include that in the following code:

    PHP Code:
    <?php require_once('../Connections/pricing.php'); ?>
    <?php 

    $result 
    mysql_query ("SELECT pack_price,pack_setup FROM package_type WHERE pack_id=21 ");

    list(
    $pack_price,$pack_setup) = mysql_fetch_array($result);

    select substring_index(
           
    substring_index(pack_price,'|',2)
                          ,
    '|',-1
              as 
    second_price

    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    <body>
    <?php echo $pack_price,$pack_setup ?>
    <?=display_currency
    (split_price($pack_price,"price",1))?></b> / <?=MONTHLY?><?=EF?>
    </body>
    </html>
    Many thanks for your time,

    Richard

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you put it into the SELECT statement --
    Code:
    $result = mysql_query ("SELECT **here** ...
    someone else may have to step in here (i don't do php, i do coldfusion)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you to everyone who has provided assistance throughout this thread it is much apprechiated. However i have one last problem

    I am unable to query two variables at the same with WHERE

    PHP Code:
    <?php $monthlyprice mysql_query ("SELECT substring_index(
           substring_index(pack_price,'|',1)
                          ,'|',-1)
              as monthly FROM package_type WHERE pack_id=
    $packageid OR pack_name=$packagename");
              
    list (
    $monthly) = mysql_fetch_array($monthlyprice);

    ?>
    Any assistance is again very much apprechiated.

    Regards,

    Richard

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if pack_name is a character datatype column, then $packagename should have quotes

    ... OR pack_name = '$packagename'

    test your query outside of php before trying to embed it in your script
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks , all working now.

    PHP Code:
    <?php require_once('../Connections/pricing.php'); ?>
    <?php 
    $packageid 
    "";
    $packagename "Starter";
    ?>

    <?php $monthlyprice mysql_query ("SELECT substring_index(
           substring_index(pack_price,'|',1)
                          ,'|',-1)
              as monthly FROM package_type WHERE pack_id='
    $packageid' OR pack_name = '$packagename'");
              
    list (
    $monthly) = mysql_fetch_array($monthlyprice);

    ?>
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    </head>
    <body>
    <?php echo $monthly ?><br />
    </body>
    </html>
    Sorry one last thing i promise

    Is there anyway i can automatically get a inserted infront of the price in the script.

    Many Thanks

    Richard

  21. #21
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry another thing is there any way i can force the script to only display the prices to two decimal places if there are more than two figures after the decimal point in the database. Rounding is not needed.

    Regards,

    Richard

  22. #22
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by richard1
    Is there anyway i can automatically get a inserted infront of the price in the script.
    Solved it:

    PHP Code:
    <?php echo $currency,$monthly;?><br />
    <?php echo $currency,$quarterly ?><br />
    <?php echo $currency,$semiannually ?><br />
    <?php echo $currency,$annually?><br />
    <?php echo $currency,$twoyears?><br />
    <?php echo $currency,$threeyears?><br />

  23. #23
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    I am now faced with a new problem is it possible to *3 the value that my script below outputs in the SQL.

    eg:
    PHP Code:
    ("SELECT substring_index(
           substring_index(pack_price,'|',2)
                          ,'|',-1) 
    The full script is included below

    Regards,

    Richard.

    PHP Code:
    /* Quarterly (3 Months) */

     
    $quarterlyprice mysql_query ("SELECT substring_index(
           substring_index(pack_price,'|',2)
                          ,'|',-1)
              as quarterly FROM package_type WHERE pack_id='
    $packageid' OR pack_name = '$packagename'");
              
    list (
    $quarterly) = mysql_fetch_array($quarterlyprice);

    /* End Quarterly (3 Months) */ 

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what the SUBSTRING_INDEX function pulls out of your pipe-delimited column value is a string

    therefore, in order to multiply it by 3, you have to convert it first, from a string to a number

    try SELECT 3 * CAST(SUBSTRING_INDEX(...) AS DECIMAL(9,2))

    or you could do the conversion in php, i'm sure (except i don't know how, as i don't do php)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SitePoint Member
    Join Date
    Feb 2006
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Am i doing this right as it comes up with Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource!

    PHP Code:
     $quarterlyprice mysql_query ("SELECT 3 * CAST(SUBSTRING_INDEX(substring_index(pack_price,'|',1)
                          ,'|',-1)) AS DECIMAL(9,2))
               FROM package_type WHERE pack_id='
    $packageid' OR pack_name ='$packagename'");
              
    list (
    $quarterly) = mysql_fetch_array($quarterlyprice); 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •