PHP - Mysql For Select Using Sum In Another Table

What is this a. b. c. before the fields? I'm not understanding it. I know is about foreign key and reference, but why a. b. c. ??

Code: [Select]
<?php
$user_id='1'; // User table user_id value
$update_sql=mysql_query("SELECT a.username, a.email, b.update_id, b.update, b.time, b.vote_up, b.vote_down FROM users a, updates b, friends c WHERE b.user_id_fk = a.user_id AND c.friend_one = '$user_id' AND b.user_id_fk = c.friend_two ORDER BY b.update_id DESC LIMIT 15");
while($row=mysql_fetch_array($update_sql))
{
$username=$row['username'];
$email=$row['email'];
$update_id=$row['update_id'];
$update=$row['update'];
$time=$row['time'];
$up=$row['vote_up'];
$down=$row['vote_down'];

//Avatar
$lowercase = strtolower($email);
$image = md5($lowercase);
$avatar ='http://www.gravatar.com/avatar.php?gravatar_id='.$image;

//Update HTML tags filter
$htmldata = array ("<", ">");
$htmlreplace = array ("&lt;","&gt;");
$final_update = str_replace($htmldata, $htmlreplace, $update);


// Updates Results Display here

}
?>

Basically, is this a good idea to use, or rather select only the fields you need for a certain reason. Say I have the fields: uid,uname,upass,usalt, and udisplayname. If a sneaky little .... somehow injected a query that is used for users that are logged in, wouldn't it be better to only have the relevant fields selected? ( In this case, uname,upass,and usalt should only be touched if adding a user, or having a user log in, because beyond that, why would you need something that's purpose is only for authenticating a user? ), or rather select all fields?.

I've been wondering this for a while. So if I used my method ( select only relevant fields ), even if a sneaky little .... did inject sql to try and get a certain user's login information, it would not give them that info because those fields are NOT selected, as opposed to selecting all fields, and having that sneaky little .... get ahold of that users info.. Still even if I used uname and upass, they'd still have to figure out that I'm using a unique salt for each user, and that even if 2 users have the same password, theyd need to do seperate rainbow tables for each password.

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!

Hi guys I have a two tables in my mysql 

branch:

id       branchname     postcode


then a user table

id    username    password   branch1     branch2


so far I have a select form as below which populates the select form from mysql

    branch 1<select name='branch1'><p />
    
<?php
$branchdropdown=mysql_query("SELECT id ,branchname, postcode FROM branch");

while($row = mysql_fetch_array($branchdropdown))
{
// echo '<option value="' .$row['stationname']. '"></option>'  ;
echo "<option value=\"".$row['id']."\">".$row['branchname']."\n  ";
$postcodeone=$row['postcode'];

}


?>

and then this will be inserted in mysql as below 


 $submit = mysql_query("INSERT INTO users (branch1, branch2) VALUES ($postcodeone, $postcodetwo)");
     echo "This entry has been added to our database";

    </select>

but it only inserts the branch id and not the password, can you please tell me what im doing wrong?

I have a MySQL database.  The table name is prodcat - it has 3 fields sku, category and codes.  Some of the categorys have multiple items in it which is seperated by /   for example:  Ceramics/Sale Items or just Ceramics   in the codes it looks like this:  ceramics,saleitems or just ceramics   Basically i want to loop through each sku in the prodcat table and every sku that is in ceramics I want it to echo an image.  And if that sku is in multiple categories it needs to show the image no matter what category you are in.  So if it is a ceramic item that is also on sale I want the image to appear in both those categories   I've tried this multiple ways and can get a partial result but can not get it completely the way I want it.  Basically I can get it to give me the result in one category but it won't give me the result in both categories.   Thanks for the 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

I have a table with a field called "tags" and below is an example of how this field might look like. 

pensions, employee benefits, group benefits, defined contribution, auto-enrollment

I want to perform a query to get this field for all of the rows in my table.  However is there a way to stop mysql from returning duplicates.  For example if another field read,

pensions, employee benefits, group benefits, defined contribution, auto-enrollment, tax

Then it would only return "tax" from this field as it's already return pensions, employee benefits etc. 

Thanks for any help. 
 

Hello,

I need some help. Say that I have a list in my MySQL database that contains elements "A", "S", "C", "D" etc... Now, I want to generate an html table where these elements should be distributed in a random and unique way while leaving some entries of the table empty, see the picture below. But, I have no clue how to do this... Any hints?



Thanks in advance,
Vero

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

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."; 
 } 
 } 
 ?>

This topic has been moved to MySQL Help.

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

HI , newbie here !

I have 4 tables ( event, price, vehicle, town)

I want to select from a database when a user chooses the town, event and number of passengers (which I have got the form working ok)

when displaying results I can get it to retrieve the $town and $event details fine,I  just need  the vehicleID column in the vehicle table to match the price.vehicleID when it is equal to $pax



$calc = ("
       SELECT* FROM price, vehicle WHERE price.townID = $town AND price.eventID = $event AND $pax == vehicle.passenger AND price.vehicleID = vehicle.vehicleID
       ");

if you have a look at www.hirealimo.com.au you will see what m trying to do.

thanks in advance

$sql = "SELECT sum(tbl1.number) FROM tbl1, tbl2";

it only works if i have one table when i add the second it doesnt work. i just need the sum in one column from one table i just need other info from the other table too it wont work tho. im getting an entirely different number for the sum.