Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jan 2012
    Location
    RI, USA
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Help with A SQL Function...

    Hi, everyone. I just joined about a week ago, great forum so far.

    What I am trying to do here is make a function that intakes a SQL command and the name of a database, and output an associative array based on the SQL results.

    PHP Code:
    function getSql($query$database) {
             include(
    "key.php"); //Username + password information for the SQL connection.
            
    $connection mysql_connect($dbhost$dbuser$dbpass) or die ("Error connecting to mysql");
            @
    mysql_select_db($database) or die("Couldn't find the database"); 
            
    $id mysql_query($query$connection) or exit("Fail SQL.");
            
    $columns mysql_num_fields($id);
            
    $rows mysql_num_rows($id);
            
            
    //Fill the array up in a column-like pattern.
            //First, get the name of the key the query gave.
            //Then as long as there are rows, fill that column the key has
            //with the data.
            
    for ($i 0$i $columns$i++) {
                for (
    $j 0$j $rows$j++) {
                     
    $row mysql_fetch_assoc($id);
                     
    $keys array_keys($row);
                     
    $assoc[$keys[$i]] = $row[$keys[$i]];
                }
            }
            
    mysql_close($connection);
            return 
    $assoc;
        } 
    When I run this code, I get this:

    Warning: array_keys() [function.array-keys]: The first argument should be an array in /home/user1/public_html/articles/sql-test.php on line 75

    In my code, line 75 has $keys = array_keys($row);. I am baffled because just one line ago, I told it to fetch an associative array, and I use that array in this line. However, it basically says it isn't an array. I even used print_r to print out the value of $row, and it comes out as an associative array of the SQL results just fine...any ideas?
    Last edited by Alternative; 01-11-2012 at 01:39 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    You've stepped into null. You only have $rows records worth of data, but you are iterating it by $rows * $columns. On an attempted fetch, false is returned, so array_keys will fail.
    Rebuild the array into the format of $array[fieldname][record] like so:
    PHP Code:
    $aRows = array();
    while (
    $row mysql_fetch_assoc($id))
    {
        
    $aRows[] = $row;
    }

    $aAssoc = array();
    foreach (
    $aRows AS $record)
    {
        foreach (
    $record AS $key => $val)
        {
            if (!isset(
    $aAssoc[$key]))
            {
                
    $aAssoc[$key] = array();
            }
            
    $aAssoc[$key][] = $val;
        }


  • #3
    New Coder
    Join Date
    Jan 2012
    Location
    RI, USA
    Posts
    24
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much, Fou-Lu. I will post this function in the useful functions area - I think this function will be very useful to others. This thread can be closed.


  •  

    Posting Permissions

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