PHP - Php/mysql Select Help

I have the following sql which works fine


"SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number group by article_number"


However I want to add to this statement to make sure that it only returns the results where the field avg(ratings.score) is between 3 and 4.  I tried the sql below but it came up with the error "invalid use of group function".


SELECT AVG(ratings.score), articles.* FROM ratings, articles where ratings.article_number = articles.article_number and AVG(ratings.score) < 4 group by article_number


Thanks for any help

Hello,

For some reason the following query returns 0 rows:


   $query = mysql_query("
SELECT * FROM `businesses_touchlocal_temp` 
WHERE `postcode` = '".$this_business['postcode']."'
LIMIT 1
")or die(mysql_error());

echo mysql_num_rows($query);
echo "<br />";
var_dump($this_business['postcode']);


Quote

0
string( "SE8 5"


But this returns 1 row:



   $query = mysql_query("
SELECT * FROM `businesses_touchlocal_temp` 
WHERE `postcode` = 'SE8 5'
LIMIT 1
")or die(mysql_error());

echo mysql_num_rows($query);


Quote

1


Kind of banging my head here. Thanks!

hello with tis button i generate some statistics from mysql


if(isset($_POST['sub1']))

                                {
           
                                    $result = mysql_query("SELECT servitoros1, COUNT(*) from history WHERE serv LIKE '%be%'  group by serv1");
                                   
                                    while($row = mysql_fetch_row($result))
                                                                           {
                                                                               


                                          echo "<tr>";
                                                                                 foreach($row as $cell)
                                                                           
                                                                            echo "<td ALIGN=\"center\">$cell<FONT></td>";   
                                                    
                                                                                    echo "</tr>\n";
                                                                               
                                                                           }
                                   
                                   
                                    mysql_free_result($result);
                 
                            }


               
                      but each record have datetime field!!


so how can i set this display betwean 2 selectable datetimes

e.g.

from 2/2/2011 15:45   to  3/2/2011 23:59

Hey guys I want to create a class containing a SELECT statment in order to make in re-usable though out my entire applicion. Any one no a good way to go about this?

This is the native way i have done it. I want to be able to make this into a class becuase i have more than one database with the same tables.
Code: [Select]
                                $articles = "SELECT * FROM blog_posts INNER JOIN users ON blog_posts.aurthor_id=users.user_id
INNER JOIN post_categories ON blog_posts.category_id=post_categories.id";
$articles_results = mysql_query($articles)or die(mysql_error());;

I'm experimenting out premade codes and trying to make them some how work the way I want to. This is "my" image uploader. For the uploader is suppose to find the image AND the code stored in mysql, but it doesn't seem to find it. any problem?
<?php 
$link = mysql_connect("localhost", "mcd", "whatanicepassword");
mysql_select_db("mcd", $link);


if (!isset($_POST["code"])) 
{
die ("Error: Not all fields complete");
}

$limit_size=5120;
$target = "skin/"; 
$target = $target . basename( $_FILES['uploaded']['name']); 
$ok=1; 
$filelol = $_FILES['uploaded']['name'];
$file_size=$_FILES['uploaded']['size'];
$filecheck = "skin/".$filelol;

//This is our size condition 

 if ($file_size >= $limit_size)
{ 
echo "Your file is too large.<br>"; 
$ok=0; 
} 
 
if (file_exists($filecheck))
{

} else {echo $filelol." does not exist in the database. Please upload at the main site.<br>";
$ok=0;}

// username and password sent from Form
$uploaded=mysql_real_escape_string($_POST['uploaded']); 
$code=mysql_real_escape_string($_POST['code']); 
$checkquery = mysql_query("SELECT * FROM user_list WHERE uploaded = '$uploaded' AND code = '$code'") or die("Error : " . mysql_error());

$num_rows=mysql_num_rows($checkquery);
if ($num_rows < 1 ) { 
          echo 'File not found in MySQL<br><br><br>';
          $ok=0;
}    
else 
{echo 'YES!';}


//This is our limit file type condition 
if (($_FILES["uploaded"]["type"] != "image/png"))
{
echo "You may only upload PNG files.<br>";
$ok=0;
} 

if ($ok==0) 
 { 
 Echo "Sorry your file was not uploaded<br>"; 
 } 
 //If everything is ok we try to upload it 
 else 
 { 
 if(move_uploaded_file($_FILES['uploaded']['tmp_name'], $target)) 
 { 
 echo "The file ".$filelol." has been uploaded"; 
 } 
 else 
 { 
 echo "Sorry, there was a problem uploading your file."; 
 } 
 } 
 ?>

guys, please help

i need balance amount of fees all students has to pay. Following is the DB structure. `feepayid` is primary key for paymentstable and paymentregister & `studentid` is primary in studentstable. I have written a query for this, please evaluate the query.

paymentstable
feepayid classid paydate paymode feeamount remarks feediscount 32 8 2012-03-06 19:32:35 1 500 hgfhf 0 31 8 2012-03-04 19:32:35 1 800 hgfhf 0 30 8 2012-02-06 19:32:35 1 1200 hgfhf 0 29 8 2012-02-06 19:32:35 1 1100 hgfhf 0
paymentregister
feepayid payid totalfee studentid 32 3 1500 2 31 2 3500 2 30 1 4500 4 29 3 5500 4
studentstable
studentid studentname 2 john 4 mathew 5 peter 6 mary
Code: [Select]
SELECT d.`studentid`, SUM(a.`totalfee` - (SELECT SUM(`feeamount`) FROM `paymentstable`  WHERE a.`feepayid` = `feepayid`)) AS balancefee
FROM `studentstable` d
LEFT JOIN `paymentregister` a ON a.`studentid` = d.`studentid`
GROUP BY d.`studentid`;

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=350716.0

I'm working on my website, and I'm having a bit of trouble in getting PHP to select the proper data.

I'm trying to select usernames from my database where rank is equal to one (the highest, in my system). As such, I attempted this code;

Code: [Select]
// Connection info above this line...
mysql_select_db("users");
$query = mysql_query ("SELECT displayname FROM login WHERE rank = 1");
$query_a = mysql_fetch_array($query);
var_dump($query_a);

The var_dump resulting from that is as follows;
Code: [Select]
array
  0 => string 'Seltzer' (length=7)
  'displayname' => string 'Seltzer' (length=7)

Everything is working correctly, except for the fact that my database contains two displayname rows where rank is equal to one (EDIT: Two distinct rows). In fact, I can run a search of it in phpMyAdmin and get the two that my PHP code should be returning. phpMyAdmin generated the following query, which Inserted into my code in order to double-check things;

Code: [Select]
SELECT  `displayname`
FROM  `login`
WHERE  `rank` =1
LIMIT 0 , 30

Even after I swapped my queries, the PHP code still returned the same var_dump as above. Complicating things further, I've noticed another function I've made, which queries "SELECT rank WHERE displayname = '$displayname'", functions perfectly.

I've gotten rid of pretty much any source of the error I could think of; I'm testing the function on an otherwise empty page, I've removed any classes being used, and I've tried about a million different queries.

Can someone help me out with this? I'm being held up by it and I'm sure that this is a simple fix.

Thanks in advance,
Dustin

have you ever wondered how to select a single coloumn from mysql using php & checking whether a value or variable exists in the resulting array... well here is the code

$busid = '5412';
$results    =   mysql_query("SELECT `myname` FROM mytable WHERE myuser = 'user'");
        while($rows = mysql_fetch_array($results))
        {   
            $colnames[] = $rows['myname'];
        }
        if(!in_array($busid, $colnames))die("Access denied");

i want to make a monthly report
  the user selects month from drop down and i must get the specified dates of that month from the DB
  I am using ajax to get the dates

The code is posted he http://codepad.org/Fck5s2zz
The attached file (delete_user.php) is the same as the code in the link above.
The same code is also posted below.

[text]
What I am trying to do is create a function that will allow me to delete a user from my mysql database.

I have an HTML select that outputs the username(s) using the function get_admin_username()
(The function is posted below)

There is also a HTML form I am using to make this happen.
(Also posted below)

In other words I would select the user I would like to delete, and click submit which will then delete the user from the database.

The problem I am having is I do not know how to pass values from the HTML select, or the form, or even use the submit button value.

I am not sure If I need to create a function to achieve this.
Basically I don't know what to do this at all for that matter.

I thought I might have gotten close but I thought wrong, and failed.
I was told (in the php irc) that my method was way off and wrong but was given no help after that.

I do have an example to show you of how I tried to accomplish this, but being told that I was wrong I did not feel that it was even worth posting it.

I am lost and have been at this for two days now.  I am a noobe but I do understand allot (I think).

Someone, anyone, please help.

Thank You,

Ryan
[/text]

Code:
Code: [Select]
<?php 

   // Session's Functions
   require_once("../includes/sessions/session.php");

   // Establish A Connection To The Database
   require_once("../includes/connection/connection.php");

   // User Defined Admin Functions
   require_once("includes/functions/admin_functions.php");

   // New User Functions
   //require_once("includes/users/delete_user.php");

   // Confirms If The User Is Logged In
   confirm_logged_in(); 

   // Document Header
   include("includes/templates/default/header.php"); 

?>


<?php
   // Gets The Admin Username
   function get_admin_username() 
      {
         global $connection;
         $query = "SELECT * FROM administration_users ";
         $query .= "ORDER BY username";
         $admin_user_set = mysql_query($query, $connection);
         confirm_query($admin_user_set);

         while ($admin_users = mysql_fetch_array($admin_user_set)) 
            {
               echo "<option value=\"username" . "\">" . $admin_users['username'] ."\n  ";
            } 
      }
?>


      <table id="structure">
         <tr>
            <td id="navigation">
               <a href="../staff.php">Return To Staff Menu</a>
               <a href="admin_content.php">Return To Admin Menu</a>
<br />
<br />
               <ul class="menu">
                  <li class="pages"><a id="page_menu" href="new_user.php">Add New User</a></li>
                  <li class="pages"><a href="edit_user.php">Edit User</a></li>
                  <li class="pages"><a href="list_users.php">List Users</a></li>
                  <li class="pages"><a href="delete_user.php">Delete User</a></li>
               </ul>
            </td>

            <td id="page">
               <h2>Remove User</h2>

<br />

               <form action="delete_user.php" name="delete_user" method="post">
                  <table>
                     <tr>
                        <th class="field_name field_padding_user_name">User Name: &nbsp;</th>

                        <td>
                           <select id="select_username" name="username_select">
                              <?php echo get_admin_username();?>
                           </select>
                        </td>
                     </tr>

                     <tr>
                        <td class="delete_user_submit" colspan="2"><input type="submit" name="submit" value="Delete User"
                             onclick="return confirm('Are You Sure You Want To Delete This User?');"/></td>
                     </tr>
                  </table>
               </form>
            </td>
         </tr>
      </table>


<?php 

   // Document Footer
   include("includes/templates/default/footer.php");

?>

MOD EDIT: [code] . . . [/code] tags added.

So I have an array of IDs that I would only like to select from a database table instead of everything in the table.

How might I go about doing that?

Hi Guys

I don't know if this is possible but can someone point me in the right direction. I have a php function which takes two inputs and returns an output. for simplicity's sake let's say it's an addition function. What I want to do is use a mysql select statement to show all the rows from a database where field1 and field2 equal '3'. Here's the sort of thing I mean.

function addNumbers($one,$two) {
return $one + $two;
}

mysql_query("SELECT * FROM table WHERE 'addNumbers(field1,field2)' = '3'");

What I actually want to do is a lot more complex than this but I am trying to understand how to make the syntax work in simple terms first.

Can anybody help?

Many Thanks
Dan

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=348309.0

Not really sure what to ask because I don't know what the problem is. Maybe just need a fresh set of eyes to find out whats wrong.
I am trying selecting content from a database table but its not working.  I am not getting any errors just a blank screen where the content should be displayed.

html
Code: [Select]
<table>
<tbody>
<tr>
<th>Topic</th>
<th>Name</th>
<th>Date</th>
  </tr>
  <?php include 'server/forum.php'; ?>
</tbody>
</table>

php
Code: [Select]
<?php

require_once('load_data.php');

$con = mysql_connect($db_host, $db_user, $db_pwd);

if (!$con)
{
die('Could not connect to database: ' . mysql_error());
}

$dbcon = mysql_select_db($database);

if (!$dbcon)
{
  die('Could not select database: ' . mysql_error());
}

$sql = "SELECT * FROM $table ORDER BY id";
$result = mysql_query($sql) or die("Error ". mysql_error(). " with query ". $sql);

if (!$result) 
{
die("Query to show fields from table failed:".mysql_error());
}

$row = mysql_fetch_array($result)

while($row)
{
echo '<tr>';    
echo '<td class="forumtd"><b>';
echo '<a href="topic.php?id='.$row['id'].'">'.stripslashes(htmlspecialchars($row['topic'])).'</a>';
echo "</b></td>";
echo '<td class="forumtd"><em>';
echo stripslashes(htmlspecialchars($row['name']));
echo "</em></td>";
echo '<td class="forumtd">';
echo date("l M dS, Y", $row['date']);
echo "</td>";
echo "</tr>";
}

mysql_close($con);

?>