PHP - Sorting A Mysql Query On Click

Say I have a table called artists with these fields (artistid, rank).   Then say I have an array called $rankadjustments with a value for each artist (like 1, 7, 3,-3, 5, 9, etc). I am doing a MYSQL query that gets the info from artists table and sorts according to the rank field like this...

$sql = "SELECT * FROM artists ORDER BY rank";

Easy enough. But what if I would like to bring that data back sorted by (rank + rankadjustment). For example, if the artist ranked 1st had a rank of "1" from mysql, but had a rankadjustment of "5" from the rankadjustment array, his "true" rank would be "6".   Again, rankadjustment is NOT a field in my table, otherwise it would obviously be simple. It's calculated on the fly and stored in an array.  FYI, the array has an index with their "artistid". For example, $rankadjustment[341][8] would be the artist with an artistid of "341" has a rank adjustment of 8.    Is there a way to do this IN the query itself? It doesn't seem possible but wanted to find out for sure. If not, what is best way to do? I assume... Get the data sorted JUST by rank and put it all into an array, then create a new array that adds rank to rankadjustment and reorder by the "new" rank amount?   I guess that wouldn't be too bad but again, wanted to make sure I'm not missing something that would allow me to do it all in the query (or just more efficiently).   Thanks!
Edited by galvin, 21 December 2014 - 09:13 PM.

Here is my code:
// Start MySQL Query for Records
$query = "SELECT codes_update_no_join_1b" .
"SET orig_code_1 = new_code_1,
       orig_code_2 = new_code_2" .
"WHERE concat(orig_code_1, orig_code_2) = concat(old_code_1, old_code_2)";
$results = mysql_query($query) or die(mysql_error());
// End MySQL Query for Records


This query runs perfectly fine when run direct as SQL in phpMyAdmin, but throws this error when running in my script??? Why is this???

Code: [Select]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= new_code_1, orig_code_2 = new_code_2WHERE concat(orig_code_1, orig_c' at line 1

I am trying to have an UPDATE query run when I click an image on a web page. I just need to have the image call the function to run the mysql from the corresponding php file. The image is a form object, which I need the php function to recognize when it is clicked, and then run the query(ies) within. I don't really know much php, so the php code is based on a snippet I have from one of my other sites, which works fine over there, but I am missing something here that is keeping it from doing the same here.

The page already loads and displays all values in a list associated with $show_id, so that variable is already being pulled from the web page in to the php code. I'm not going to paste the entire books.php file here as there are too many things it does which doesnt have anything to do with this little add-on I am making. I just need to be able to pull that variable in to the query below and have it run when I click on the icon associated with it. The php file also already has the proper config and settings files linked to it that connect to the database, so I do not need to set that up here. It is just a form, calling a function, and running the query. It is also not visible to the user side. It is in an admin page, only visible and accessible to me.

This is code I have in the php file:

    function UpdateAllShowBooks()
    {
    if(strlen(($_POST['retired'])) > 0){

    $sql = "UPDATE ttb_books SET status_id = '2' WHERE show_id='$show_id'";
    $db = new database();
    $db->myquery($sql, 1);
    header('Location:books.php?show_id=$show_id');
    die();
        }
        
    if(strlen(($_POST['upcoming'])) > 0){

    $sql = "UPDATE ttb_books SET status_id = '37' WHERE show_id='$show_id'";
    $db = new database();
    $db->myquery($sql, 1);
    header('Location:books.php?show_id=$show_id');
    die();
        }
        
    if(strlen(($_POST['current'])) > 0){

    $sql = "UPDATE ttb_books SET status_id = '1' WHERE show_id='$show_id'";
    $db = new database();
    $db->myquery($sql, 1);
    header('Location:books.php?show_id=$show_id');
    die();
        }
    }

This is the current image/form html:

              <center><table width=25%>
              <tr><td valign="top" align="center">
              <!--<form method="post" action="books.php?show_id={show_id}">-->
              <form method="post" action="books.php" name="retired">
              <input type="image" src="../images/icon-Red_Light.png" width="20" name="retired" alt="Set status to Out of Print" title="Set status to Out of Print" onclick="return confirm('Set ALL status to Out of Print?')">
              <input name="shid1" type="hidden" id="shid1" value="{show_id}" />
              </form>
              </td><td valign="top" align="center">
              <form method="post" action="books.php" name="upcoming">
              <input type="image" src="../images/icon-Yellow_Light.png" width="20" name="upcoming" alt="Set status to Upcoming Release" title="Set status to Upcoming Release" onclick="return confirm('Set ALL status to Upcoming Release?')">
              <input name="shid2" type="hidden" id="shid2" value="{show_id}" />
              </form>
              </td><td valign="top" align="center">
              <form method="post" action="books.php" name="current">
              <input type="image" src="../images/icon-Green_Light.png" width="20" name="current" alt="Set status to In Print" title="Set status to In Print" onclick="return confirm('Set ALL status to In Print?')">
              <input name="shid3" type="hidden" id="shid3" value="{show_id}" />
              </form>
              </td></tr>
              </table></center>

So clicking on the form image would call the function with that mysql string and update all items in the list accordingly. At this time, when I click the image, it reloads the page fine, but it is not activating the sql in the function and making the Update occur. Thank you for any help you can provide. I really appreciate it.

If you also have any feedback on my code, please do tell me. I wish to improve my coding base.

Basically when you fill out the register form, it will check for data, then execute the insert query. But for some reason, the query will NOT insert into the database. In the following code below, I left out the field ID. Doesn't work with it anyways, and I'm not sure it makes a difference.

Code:

  Code: [Select]
mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
Full code:

Code: [Select]
<?php
include_once("includes/config.php");
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title><? $title; ?></title>
<meta http-equiv="Content-Language" content="English" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" type="text/css" href="style.css" media="screen" />
</head>
<body>
 
<div id="wrap">
 
<div id="header">
<h1><? $title; ?></h1>
<h2><? $description; ?></h2>
</div>
 
<? include_once("includes/navigation.php"); ?>
 
<div id="content">
<div id="right">
<h2>Create</h2>
<div id="artlicles">
<?php
 
if(!$_SESSION['user'])
{
 
        $username = mysql_real_escape_string($_POST['username']);
        $password = mysql_real_escape_string($_POST['password']);
        $name = mysql_real_escape_string($_POST['name']);
        $server_type = mysql_real_escape_string($_POST['type']);
        $description = mysql_real_escape_string($_POST['description']);
 
        if(!$username || !$password || !$server_type || !$description || !$name)
        {

        echo "Note: Descriptions allow HTML. Any abuse of this will result in an IP and account ban. No warnings!<br/>All forms are required to be filled out.<br><form action='create.php' method='POST'><table><tr><td>Username</td><td><input type='text' name='username'></td></tr><tr><td>Password</td><td><input type='password' name='password'></td></tr>";
        echo "<tr><td>Sever Name</td><td><input type='text' name='name' maxlength='35'></td></tr><tr><td>Type of Server</td><td><select name='type'>
       
        <option value='Any'>Any</option>
        <option value='PvP'>PvP</option>
        <option value='Creative'>Creative</option>
        <option value='Survival'>Survival</option>
        <option value='Roleplay'>RolePlay</option>
       
        </select></td></tr>
       
        <tr><td>Description</td><td><textarea maxlength='1500' rows='18' cols='40' name='description'></textarea></td></tr>";
        echo "<tr><td>Submit</td><td><input type='submit'></td></tr></table></form>";
        }
        elseif(strlen($password) < 8)
        {
        echo "Password needs to be higher than 8 characters!";
        }
        elseif(strlen($username) > 13)
        {
                echo "Username can't be greater than 13 characters!";
        }
        else
        {
                $check1 = mysql_query("SELECT username,name FROM servers WHERE username = '$username' OR name = '$name' LIMIT 1");
               
                if(mysql_num_rows($check1) < 0)
                {
                        echo "Sorry, there is already an account with this username and/or server name!";
                }
                else
                {
                        $ip = $_SERVER['REMOTE_ADDR'];

                        mysql_query("INSERT INTO servers (username, password, name, type, description, ip, votes, beta) VALUES ($username, $password, $name, $server_type, $description, $ip, 0, 1)");
                        echo "Server has been succesfully created!";
                }
        }
       
}
else
{
        echo "You are currently logged in!";
}
 
?>
</div>
</div>
 
<div style="clear: both;"> </div>
</div>
 
<div id="footer">
<a href="http://www.templatesold.com/" target="_blank">Website Templates</a> by <a href="http://www.free-css-templates.com/" target="_blank">Free CSS Templates</a> - Site Copyright MCTop
</div>
</div>
 
</body>
</html>

I'm restarting this under a new subject b/c I learned some things after I initially posted and the subject heading is no longer accurate.

What would cause this behavior - when I populate session vars from a MYSQL query, they stick, if I populate them from an MSSQL query, they drop.

It doesn't matter if I get to the next page using a header redirect or a form submit.  I have two session vars I'm loading from a MYSQL query and they remain, the two loaded from MSSQL disappear. 

I have confirmed that all four session vars are loading ok initially and I can echo them out to the page, but when the application moves to next page via redirect or form submit, the two vars loaded from MSSQL are empty. 

Any ideas?

Hi - i need help with the fourth column named "jobnr" it has to be the highest number first and lowest at the bottum of the tabel.
I tried different variation like  "mysql_query("SELECT * FROM tabel ORDER BY jobnr DESC")" but with no success.
I attached a picture og the working script output 


Code: [Select]
<html>

<head>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8"/>
</head>
<style type="text/css">
.myclass
{
 font-size: 8pt;
 font-face: Verdana;
 }
 </style>
 
<body>




<?php

// Define variables

$host="host"; // Host name 
$username="user"; // Mysql username 
$password="password"; // Mysql password 
$db_name="database"; // Database name 
$tbl_name="tabel"; // Table name

// Connect to server and select databse
mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");

// saetter db udtraek til UTF-8 endcoding
mysql_set_charset('utf8');


// henter db data fra tabllen: jobpositons

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);


// Define $color=1 
$color="1";


echo '<table border=0" bordercolor="#f3f3f3" cellpadding="1" cellspacing="1">';

echo "<tr bgcolor='#00aeef'> <th>Jobtitel</th> <th>Sted</th> <th>Oprettet</th> <th>jobnr</th> </tr>";

// sortere sql db data og indsaetter i html tabel


while($rows=mysql_fetch_array($result))

{

// If $color==1 table row color = #ffffff
if($color==1){
echo "<tr bgcolor='#ffffff'><td class='myclass'>".$rows['Jobtitel']."</td><td class='myclass'>".$rows['Sted']."</td><td class='myclass'>".$rows['oprettet']."</td><td class='myclass' align='right'>".$rows['jobnr']."</td>
</tr>";
// Set $color==2, for switching to other color 
$color="2";
}

// When $color not equal 1, use this table row color 
else {
echo "<tr bgcolor='#f3f3f3'>
<td class='myclass'>".$rows['Jobtitel']."</td><td class='myclass'>".$rows['Sted']."</td><td class='myclass'>".$rows['oprettet']."</td><td class='myclass' align='right'>".$rows['jobnr']."</td>
</tr>";
// Set $color back to 1 
$color="1";
}

}
echo '</table>';

mysql_close();
?>
</body>
</html>

Hi,

How can I get others informations on another page by clicking on one row's value

I have this code, but it doesn't work:


while($row = mysql_fetch_array($result))
  {
 echo "<tr><p>";
 
  echo "<th><p>" . $row['fname'] . "&nbsp;&nbsp;&nbsp;" . $row['lname'] . "</p></th>";
  echo"<th><p><a href='student.php?id='".$row['topic']."'\'>".$row['topic']."</p></a></th>";

  echo "</tr>";
  }
echo "</table>";


Student.php


<?php
$con = mysql_connect("localhost","root","");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("college", $con);

$result = mysql_query("SELECT * FROM students");
$topic = $_Get['topic'];
while($row = mysql_fetch_array($result))
  {
  
 echo "<tr><p>";
  echo "<th><p>" . $row['month']."&nbsp;" .$row['datetime']."</p></th>";
  echo"<th><p>". $row['topic'] ."</p></th>"; 
  echo "<th><p>" . $row['gender'] . "</p></th>";
  echo "<th><p>" . $row['fname'] . "&nbsp;&nbsp;&nbsp;" . $row['lname'] . "</p></th>";
  echo "<th ><p>" . $row['id'] . "</p></th>";
  echo "</tr>";
  
echo "</table>";

mysql_close($con);
  }
?> 

I have 2 tables (see code below)
 (1) "tbl_users" has all users info, including "user_alias" (VARCHAR) and "user_id" (INT and PRIMARY KEY)
 (2) "tbl_projects" has all project info, including "project_client" (INT) which i linked to "user_id" from "tbl_users" and displays "user_alias"

Code: [Select]
CREATE TABLE `tbl_users` (
  `user_id` int(11) NOT NULL auto_increment,
  `user_first` varchar(25) NOT NULL,
  `user_last` varchar(25) NOT NULL,
  `user_email` varchar(35) NOT NULL,
  `user_pw` varchar(12) NOT NULL,
  `user_role` int(1) NOT NULL,
  `user_alias` varchar(35) NOT NULL,
  PRIMARY KEY  (`user_id`)
)

CREATE TABLE `tbl_projects` (
  `project_id` int(11) NOT NULL auto_increment,
  `project_client` int(1) NOT NULL,
  PRIMARY KEY  (`project_id`)
)

So... I managed to display all users and all projects but I want to show all users with ONLY their projects assigned. I mean, when I click on a user, I want to see his or hers contact info and all the projects that specific user is linked to.

For instance, i might have project 1, project 2, project 3 and project 4. Project 1, 2 and 4 are linked to user 1 so when i click on user 1 i see projects 1, 2 and 3 (not 4).

Code: [Select]
<?php 
require_once('config.php');
mysql_select_db($database, $makeconnection);

//this displays all projects
$sql_get_projects="SELECT *
FROM tbl_projects
ORDER BY project_id ASC";
$get_projects = mysql_query($sql_get_projects, $makeconnection) or die(mysql_error());
$row_get_roles = mysql_fetch_assoc($get_projects);
$totalRows_get_projects = mysql_num_rows($get_projects);

//this displays all users
$sql_find_users = "SELECT * 
FROM tbl_users 
WHERE user_id = $user_id";
$find_users = mysql_query($sql_find_users, $makeconnection) or die(mysql_error());
$row_get_users = mysql_fetch_assoc($find_users);
$totalRows = mysql_num_rows($find_users);

?>

Any help would be great!!

I have this query that searches the database based on what the user inputed.

However i'm having the following issues with it:


The query is supposed to look for the name of a city in a database table that stores the city names along with their state and country

CITY STATE COUNTRY
Now i have an input field where the user can search a location and it searches the location from the database. When they search the database the input is in the following format:

city, state, country

I then use the following script to separate them into three fields
Code: [Select]
$split = explode(',', $location); // $location being the city, state, country
$city = $split[0];
$state = $split[1];
$country = $split[2];

Then i use this MySql Query to search the fields to make sure the city and country match
Code: [Select]
$get_location = mysql_query("SELECT * FROM locations WHERE name LIKE '$city' AND country LIKE '$country'") or die(mysql_error());
$tmp_loc = mysql_fetch_assoc($get_location);

This is where the problem beings...if i use "AND" to search both fields i get no results even though there are results in there however if i change it to an "OR" statement it finds teh locations however it doesn't do an accurate search...

for example someone searches for Toledo, Ohio, United States, the user will get Toledo, Spain instead of the right Toledo, since Toled, Spain is at the top of the table

I've been trying to get this but i can't get this to work at all every time i use AND it gives me no results

I hope someone here can check help me out

Hello,
I have a query where i try to search, but i want to put a limitation, but it doesn't seems to be working :/
Here's my code:

$result = mysql_query("SELECT * FROM clients WHERE staff = 0 AND username LIKE '%" . $keyword .  "%' OR company LIKE '%" . $keyword .  "%' ORcontact LIKE '%" . $keyword .  "%' OR address LIKE '%" . $keyword .  "%' OR email LIKE '%" . $keyword .  "%' OR phone LIKE '%" . $keyword .  "%' ORDER BY phone");


Alright, so i basically want the user to search in all of those fields, however i want it to filter all "staff" members, so it should only view the "0" ones, meaning the clients, only problem that when i run this, all clients gets displayed, also the staff accounts.

Any suggestion?

Hello!

Please help... I am trying to use the script below to get results from a mysql database based on a query of the form fields (the names of which are displayed near the top of the script as POST items)

When a location or age etc. is entered into the form, I want the script to search for records which meet those criteria. At the moment the script works but only does so if all the values are entered to match what is in the database. e.g. if the location england and the age 22 was entered into the form, and that matched the value in the database, then at the moment, the script will display the result, but if only the location is entered in the form without any value for age/genre etc. then no results are displayed.

Any help would be very welcome as I have search high and low for a solution on google... which doesn't seem to exist...

I'm not that experienced with php/mysql but am learning on the job so any helpful prompts as to terms etc. would help!

Thanks!

Lewis

<?php 

if($_POST) 
{ 
$searchage = $_POST['searchage'];
$searchlocation = $_POST['searchlocation'];
$searchgenre = $_POST['searchgenre']; 
$searchinstrument = $_POST['searchinstrument']; 
$searchexperience = $_POST['searchexperience']; 
 
// Connects to your Database 
mysql_connect("localhost", "user", "pass") or die(mysql_error()); 
mysql_select_db("DB") or die(mysql_error()); 

$query = mysql_query("SELECT * FROM table_user WHERE userage = '".$searchage."' AND userlocation = '".$searchlocation."' AND usergenre = '".$searchgenre."' AND userinstrument = '".$searchinstrument."' AND userexperience = '".$searchexperience."'") or die(mysql_error()); 

$num = mysql_num_rows($query); 

echo "$num results found!<br>"; 

while($result = mysql_fetch_assoc($query)) 
{ 
$username = $result['username']; 
$useremail = $result['useremail'];
$userage = $result['userage'];
$userlocation = $result['userlocation'];
$usergenre = $result['usergenre'];
$userinstrument = $result['userinstrument']; 
$userexperience = $result['userexperience']; 
$userbiography = $result['userbiography']; 

echo "
Name: $username<br> 
Email: $useremail<br>
Age: $userage<br>
Location: $userlocation<br>
Gen  $usergenre<br>
Instrument: $userinstrument<br>
Experience: $userexperience<br>
Biography: $userbiography<br><br>       
"; 
} 


} 

?> 

I have a query which when I run in phpmyadmin it returns the results I want.

When I put it into PHP I get no results can someone tell me what I'm doing wrong?

Code: [Select]
<?php include("config.php"); ?>
<?php
// sending query
$sql = mysql_query("SELECT dayname((date(FROM_UNIXTIME(dateline)))) as 'Day Of Week', date((date(FROM_UNIXTIME(dateline)))) as 'Date', count(*) as 'Number of Opened Tickets', ( select count(ticketmaskid) from swtickets where date(FROM_UNIXTIME(swtickets.lastactivity)) = Date and isresolved=1 ) as 'Number of Closed Tickets' from swtickets where ((date(FROM_UNIXTIME(dateline)) between (DATE_SUB(CURDATE(), INTERVAL (IF(DAYOFWEEK(CURDATE())=1, 9, DAYOFWEEK(CURDATE()))) DAY)) and (DATE_ADD(CURDATE(), INTERVAL (6 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY)) )) group by date(FROM_UNIXTIME(dateline))");
?>

<?php echo $sql; ?>

All it returns is: Resource id #4

When in phpmyadmin I get:

I am creating a site that has to display 36 images on the screen.  The image name is stored in the database.  My problem is if I have less than 36 images stored I need to display a default image.  here is my current query

$sql="SELECT col_image, col_url from tbl_images WHERE col_active='1' and col_bigimage='0' ORDER BY RAND() limit 36";

so If I only have 20 active images. I need to display 16 default images.  I hope this makes sense.

Bill

hi dudes
how do i write a mysql query with 3 columns, where the first column is 'year', the second is 'month' (integer) and the third is 'day' (integer), ordered by desc, but with an extra quirk, where if any of the three columns is zero (which means there is no data for that date column - assume i have a year and a month, but no day)?

my code looks like the following
Code: [Select]
ORDER BY exhib_date_year DESC, exhib_date_month DESC, exhib_date_day DESC