PHP - Mysql Select Not Working

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 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. 
 

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

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.

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.

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!

I need to select all fields from two tables and echo that data.
Fields are the same in both tables but has different data.


<?PHP
include "dbconnect.php";

function pretvoriDatum($mysqlDatum)
{
$tmp=explode("-", $mysqlDatum);
$datum=$tmp[2] . "." . $tmp[1] . "." . $tmp[0];
return $datum;
}

$sql="SELECT * FROM novosti A, dogadanja B";
if (!$q=mysql_query($sql))
{
echo "Error" . mysql_query();
die();
}
if (mysql_num_rows($q)==0)
{
echo "No data</div>";
} else {
?>
<?PHP
while ($redak=mysql_fetch_array($q))
{
?>



<?php echo $redak["a.naslov"]; ?>

<?PHP echo $redak["a.slika"]; ?>

<?php echo $redak["b.tekst"]; ?>

<?php echo $redak["a.objavio"]; ?>

<?PHP echo pretvoriDatum($redak["b.datum"])?>


<?PHP
}
}
?>


Please help, I don't know how to do that

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

}
?>

Hi, I am geting questions marks in place of some characters. However, this only happens on my own queries and echo statements, the same database read by wordpress handles them fine.

Can anyone tell me why?

I have added UTF-8 headers and meta tags, the database is in UTF-8 encoding. I just can't figure out why wordpress can manage it and my php cant. I have tried htmlentities with no luck

the offending page:
http://subverb.net

the wordpress blog echoing out the exact same title from the same database
http://nottingham.subverb.net/blog/sounddhism/


Thankyou

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());;

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`;

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

This topic has been moved to MySQL Help.

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

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.