Results 1 to 9 of 9

Thread: Problem with simple query

  1. #1
    Join Date
    Aug 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Problem with simple query

    I am having a problem with this simple query. The query runs fine in phpMyAdmin but does not generate a result when using this php script. The user generates a search [name1] from a separate html form. Any ideas ? Thanks

    PHP Code:
    <table width="790" border="0", cellpadding="0" cellspacing="0"background="/members/Pictures/ceder.jpg" >
      <tr valign="top" > 
        <td width="90"><font size="+2">Year</td>  
        <td width="385"><font size="+2">Competition</td>  
        <td width="315"><font size="+2">Winner</td>
      </tr>

    </tr>        

    <?php
    $hostname 
    "localhost"// DBserver.
    $username "axxxxxxx_Jerry"// DBusername
    $password "zzzzzzzz"// DBpassword
    $usertable "exp_channel_data"// TBLname
    $dbName "xxxxzzzz"// DBname

    $conn mysql_connect("localhost","axxxxxx_Jerry","xxxxxx") or die ('Error connecting to mysql');
    $dbName 'xxxxzzzz';
    mysql_select_db($dbName);

    echo 
    "Connected to Database, searching for winner named ";
    echo 
    $_POST['name1']
    ?>
    <?php
    //error message (not found message)begins
    $XX "No Record Found, to search again please close this window";
    //query details table begins



    $q_str "SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%'";
    while (
    $row = @mysql_fetch_array($query))
    {

    $variable1=$row["field_id_6"];
    $variable2=$row["field_id_7"];
    $variable3=$row["field_id_8"];
    //table layout for results

    print ("<tr>");
    print (
    "<td>$variable1</td>");
    print (
    "<td>$variable2</td>");
    print (
    "<td>$variable3</td>");
    print (
    "</tr>");
    }
    //below this is the function for no record!!
    if (!$variable3)
    {
    print (
    "$XX");
    }
    //end
    ?>
    </table>
    Last edited by djr33; 08-15-2010 at 09:16 PM. Reason: Remove passwords

  2. #2
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,419
    Thanks
    103
    Thanked 117 Times in 115 Posts

    Default

    please remove your passwords!

    Just a thought, but try replacing $query with $q_str. and remove the @ to make errors visible. Also add:
    Code:
    $name1 = $_POST['name1'];
    after
    Code:
    echo $_POST['name1'];
    and replace:
    Code:
    $variable1=$row["field_id_6"]; 
    $variable2=$row["field_id_7"]; 
    $variable3=$row["field_id_8"];
    with
    Code:
    $variable1=$row['field_id_6']; 
    $variable2=$row['field_id_7']; 
    $variable3=$row['field_id_8'];
    there are some other errors, but they are rather minor.
    Last edited by james438; 08-15-2010 at 06:04 AM.
    To choose the lesser of two evils is still to choose evil. My personal site

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Hi James
    I have made these changes but unfortunately it's behaving exactly the same; could you suggest anything ?
    thanks

  4. #4
    Join Date
    Jan 2007
    Location
    Davenport, Iowa
    Posts
    2,419
    Thanks
    103
    Thanked 117 Times in 115 Posts

    Default

    Could you post your updated code and a sample row from your table that should be showing up with your query? I want to see if I can recreate your problem on my end and also to see if you made the performed the changes I suggested correctly.
    To choose the lesser of two evils is still to choose evil. My personal site

  5. #5
    Join Date
    Aug 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Here is the code:

    Code:
    <table width="790" border="0", cellpadding="0" cellspacing="0"background="/members/Pictures/ceder.jpg" >
      <tr valign="top" > 
        <td width="90"><font size="+2">Year</td>  
        <td width="385"><font size="+2">Competition</td>  
        <td width="315"><font size="+2">Winner</td>
      </tr>
    </tr>       
    
    <?php
    $hostname = "localhost"; // DBserver.
    $username = "username"; // DBusername
    $password = "password"; // DBpassword
    $usertable = "exp_channel_data"; // TBLname
    $dbName = "database"; // DBname
    
    $conn = mysql_connect("localhost","username","password") or die ('Error connecting to mysql');
    $dbName = 'database';
    mysql_select_db($dbName);
    
    echo "Connected to Database, searching for winner named ";
    echo $_POST['name1'];
    $name1 = $_POST['name1'];
    ?>
    <?php
    //error message (not found message)begins
    $XX = "No Record Found, to search again please close this window";
    //query details table begins
    
    $q_str = "SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%'";
    while ($row = @mysql_fetch_array($q_str))
    {
    
    $variable1=$row['field_id_6'];
    $variable2=$row['field_id_7'];
    $variable3=$row['field_id_8'];
    //table layout for results
    
    print ("<tr>");
    print ("<td>$variable1</td>");
    print ("<td>$variable2</td>");
    print ("<td>$variable3</td>");
    print ("</tr>");
    }
    //below this is the function for no record!!
    if (!$variable1)
    {
    print ("$XX");
    }
    //end
    ?>
    </table>
    You can try it at: http:www.aegc.co.uk/members/search-form.html. Try searching on Dixon [this should produce about 15 rows but comes up empty]
    many thanks
    Last edited by JerryDi; 08-17-2010 at 09:08 AM.

  6. #6
    Join Date
    Feb 2010
    Location
    Falkirk, Scotland
    Posts
    142
    Thanks
    21
    Thanked 4 Times in 4 Posts

    Default

    try removing @ in:
    PHP Code:
    while ($row = @mysql_fetch_array($q_str)) 

  7. #7
    Join Date
    Aug 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    doing this produces the following error

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

  8. #8
    Join Date
    Mar 2006
    Location
    Illinois, USA
    Posts
    12,164
    Thanks
    265
    Thanked 690 Times in 678 Posts

    Default

    That means that either there is an error in the query (it is invalid) or that it doesn't match your database: for example, you may have a typo in one of the column names or you may be looking for a data type (integer, for example) in a column that doesn't support it (maybe only text, for example).
    Daniel - Freelance Web Design | <?php?> | <html>| espa˝ol | Deutsch | italiano | portuguŕs | catalÓ | un peu de franšais | some knowledge of several other languages: I can sometimes help translate here on DD | Linguistics Forum

  9. #9
    Join Date
    Aug 2010
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Hi
    I knew there was nothing wrong with the query so I tried a different approach and BINGO it works
    Here is the code

    PHP Code:
    <html>
    <body>
    <?php
    $username
    =" ";
    $password="  ";
    $database=" ";

    mysql_connect(localhost,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");


    echo 
    "Connected to Honours Board Database....... results for winner named ";
    echo 
    $_POST['name1'];
    $name1 $_POST['name1'];

    $query="SELECT field_id_8, field_id_7, field_id_6 FROM exp_channel_data WHERE field_id_8 LIKE '%$name1%' ORDER BY field_id_7 ASC, field_id_6 DESC";
    $result=mysql_query($query);

    $num=mysql_numrows($result);

    mysql_close();
    ?>
    <table width="690" border="0", cellpadding="0" cellspacing="0" background="/members/Pictures/ceder.jpg" >
    <tr>
    <th><div align="left"><font face="Arial, Helvetica, sans-serif">Year</font></div></th>
    <th><div align="left"><font face="Arial, Helvetica, sans-serif">Competition</font></div></th>
    <th><div align="left"><font face="Arial, Helvetica, sans-serif">Winner</font></div></th>
    </tr>
    <?php
    $i
    =0;
    while (
    $i $num) {

    $f1=mysql_result($result,$i,"field_id_6");
    $f2=mysql_result($result,$i,"field_id_7");
    $f3=mysql_result($result,$i,"field_id_8");
    ?>
    <tr> 
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $f1?></font></td>
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $f2?></font></td>
      <td><font face="Arial, Helvetica, sans-serif"><?php echo $f3?></font></td>
    </tr>

    <?php
    $i
    ++;
    }
    ?>
    </body>
    </html>
    Thanks for the help

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
  •