WordPress.org

Ready to get started?Download WordPress

Forums

[resolved] Simple MySQL Query (15 posts)

  1. bmd91
    Member
    Posted 1 year ago #

    $qry = "SELECT *
            FROM <code>wp_companies</code>
            WHERE 1=1
            AND 'company_name' = 'Acme'";
            $data = mysql_query($qry);
            while($r[]=mysql_fetch_array($data));
    
    echo "<pre>".print_r ($r,true)."</pre>";

    Anybody know why this would be returning an empty array? It seems so straightforward. No errors. Nothing in php error log either. When I put the sql statement into phpmyadmin it retrieves the correct row. Am I just missing something really simple? Sorry ahead of time if I am haha

  2. vtxyzzy
    Member
    Posted 1 year ago #

    I don't think the code tags should be there. Try the FROM statement like this:

    FROM wp_companies
  3. bmd91
    Member
    Posted 1 year ago #

    Empty with or without tags

  4. vtxyzzy
    Member
    Posted 1 year ago #

    Definitely leave off the tags. Try taking out the AND clause - just retrieve all rows. Also notice that your while statement should be modified.

    $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1";
    $data = mysql_query($qry);
    while($r[]=mysql_fetch_array($data));
    echo "<pre>".print_r ($r,true)."</pre>";

    EDIT: Wrong about while statement. Ignore that part.

  5. bmd91
    Member
    Posted 1 year ago #

    Well if I do that it retrieves all the rows. Why would the whole query work in phpmyadmin though? Also, I get :

    Array
    (
        [0] => Array
            (
                [0] => 2
                [ID] => 2
                [1] => 1-800 Pet Supplies
                [company_name] => 1-800 Pet Supplies
                [2] => 0
                [inventory] => 0
                [3] => 0
                [worth] => 0
                [4] => http://www.google.com
                [value_check_url] => http://www.google.com
            )

    How do I get the 0,1,2,3,4 values out so it's just id,co name, inventory, etc.?

  6. vtxyzzy
    Member
    Posted 1 year ago #

    Change the call to mysql_fetch_array to only return the associative array, and modify the while loop to print out the results:

    while($r = mysql_fetch_array($data,MYSQL_ASSOC)) {
       echo "<p>NAME:{$r['company_name']}</p>";
    }
  7. bmd91
    Member
    Posted 1 year ago #

    Ok so I have all the results by doing:

    function getCompanyData($company){
            $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1";
            $data = mysql_query($qry);
            while($r[]=mysql_fetch_array($data,MYSQL_ASSOC)){
                for ($i=0; $i < count($r); $i++) {
                    $name=$r[$i]['company_name'];
                }
            echo 'Name:'.$name.'<br />';
            };
        }

    But how would I be able to call getCompanyData('Acme') and get only their data?

  8. vtxyzzy
    Member
    Posted 1 year ago #

    It should work if coded like this:

    function getCompanyData($company){
            $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1
            AND company_name = '$company' ";
            while($r = mysql_fetch_array($data,MYSQL_ASSOC)) {
               echo "<p>NAME:{$r['company_name']}</p>";
            }
    }
  9. bmd91
    Member
    Posted 1 year ago #

    Haha I started this topic because that wasn't working..remember?

  10. bmd91
    Member
    Posted 1 year ago #

    I had

    function getCompanyData($company){
            $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1
            AND company_name = '$company' ";
            $data = mysql_query($qry);
            $r[]=mysql_fetch_array($data,MYSQL_ASSOC);

    to start

  11. vtxyzzy
    Member
    Posted 1 year ago #

    That does not match the code you showed in the original post. The original code you showed had quotes around company_name in the query:

    AND 'company_name' = 'Acme'";
  12. bmd91
    Member
    Posted 1 year ago #

    It's still direct insertion into the sql statement that is not working. Right now I have:

    function getCompanyData($company){
            $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1
            AND company_name = '$company' ";
            $data = mysql_query($qry);
            while($r = mysql_fetch_array($data,MYSQL_ASSOC)) {
               echo "<p>NAME:{$r['company_name']}</p>";
            }
    }

    It is echoing out an empty array. Sorry for the confusion

  13. vtxyzzy
    Member
    Posted 1 year ago #

    Then I suspect that either the value in $company is not what you expect, or that there are no companies with that name. Try echoing out the name and the sql:

    function getCompanyData($company){
            $qry = "SELECT *
            FROM wp_companies
            WHERE 1=1
            AND company_name = '$company' ";
    $co = htmlentities($company);
    echo "<p>CO:$co== QRY:$qry== </p>";
            $data = mysql_query($qry);
  14. bmd91
    Member
    Posted 1 year ago #

    I looked back at where I was calling getCompanyData and saw I was calling a string. I set a variable equal to the string and then called the variable. Works now. Oops sorry

  15. vtxyzzy
    Member
    Posted 1 year ago #

    Glad you found it.

Topic Closed

This topic has been closed to new replies.

About this Topic

Tags