PHP - Semi-complicated Sql Query In Php

I have inherited this database and can't change the design.

I need to perform a SELECT query and group certain records together for matching employeeID values.

The fields:
employeeID
field1
field2
date

(1) field1 can be 1,2,3,4 or 5 only

(2) field2 can be a,b,c or a number from 1-100 (varchar though)

(3) if field1 and field2 are blank/empty then group together


So:

employeeID / field1 / field2 / date

32 / 3 /   /  1297184426
45 /   /  b  /  1248723499
32 / 3 /   /  1258762988
20 /   /   / 
20 /   /   /  1268722384


So record 1 and 3 above will be grouped, and so will 4 and 5.

When I mean grouped, I mean so that only one record will be returned / displayed.

Also, when it displays only one record due to being 'grouped', I would like it to return the highest 'date' field value (UNIX Timestamp value) (so the newest / latst date) OR if one of the records is empty then show this instead. So in the above example, record 1 & 3 will return a date of 1297184426 and records 4 and 5 will return nothing for date.

<?php
echo $myname;
$myname = "Bradery";
?>

That does not work.. is there any way to get it to work without doing this

<?php
$myname = "Bradery";
echo $myname;
?>

Ok i have been after the solution for this for a while and I have finally got it in a state where it is producing results but....... they are wrong so I will start from the beginning with what I have and what I am trying to do.

I have these tables

Members
Id                        Name

Clubs
ID                       Name

Permission
ID                      Name

Link
MemberID                   PermissionID                      ClubID

When a member joins a club thier ID ant the ID of the club are entered into the link table along with their permission (as a number)
so

MemberID                             PermissionID                    ClubID
01                                                  02                              01


I have a loop which is going to echo the clubs a member is a part of. So for my querys

function GetUser($user)

 {

$qFindUser = "SELECT * FROM members WHERE email = '$user'";

 

$rFindUser = mysql_query($qFindUser);

  

$UserInfo = mysql_fetch_array($rFindUser);

return $UserInfo;

  }

 function GetLinkByMemberID($link) {
 

$qLink = mysql_query("SELECT * FROM link WHERE memberID = '$link'");

$Link = mysql_fetch_array($qLink);

return $Link;

 }

 function GetClub1($clubs)

 {

$qFindClub = mysql_query("SELECT * FROM clubs WHERE clubID = '$clubs'");

 
 return $qFindClub;
 }

 function GetPermission($per) {
 

$qPermission = mysql_query("SELECT * FROM `permissions` WHERE permissionID = '$per'");

$permission = mysql_fetch_array($qPermission);

return $permission;

 }

$User = GetUser($_SESSION['Username']); // returns as array from the login details all member details
$Link = GetLinkByMemberID($User['memberID']); // returns as a fetch array all clubs the member is a part of
$Club = GetClub1($Link['clubID']);  // returns as the query
$permission = GetPermission($Link['permission']); //returns as array so I can echo the permission word not ID


Now the loop is limited by 3 so

<?php 

$Club = mysql_fetch_array($Club);
  $stack3 = ($Club+$Link);

for($i = 0; ($CP = ($stack3)) && $i < 3; $i++ ){ ?>
  <tr>
    <th scope="col"><img name="" src="" width="32" height="32" alt="" /></th>
    <th scope="col"><?php  echo $CP['name']; //from the club table
 ?><span class="ownertext">
<?php
echo $CP['permission']; //from the link table

 ?>


The problem I'm having the the loop is returning the first record its selects 3 times and not echoing each club once.  I have worked on it for so long I can no longer make sense of it.  Can anyone see my errors and point out just how to fix it? Im guessing it involves something to do with how Im finding my permission but I really dont know.

As im sure you can see from the code Im a php beginner so Im struggling to make sense of this.

I have a list of id's between 1 - 8 which represents a print size that I need to use to calculate shipping costs, but the calculation that I need to perform is complicated. I will try to explain to the best of my abilities. I also need to query a database table to make sure the shipping rates are always up-to-date

id's <= 4 will ship for $4.95 now matter how many id's are listed. Eg:
1,2,3,4,3,2,4,2,4 will ship for a total of $4.95
will query price db as 1

id's <= 5 will ship for $6.00 now mater how many id's are listed. Eg:
1,3,3,1,2,3,5,5,5,4,3,3 will ship for a total of $6
will query price db as 2

id's <= 6 will ship for $8.00 now mater how many id's are listed. Eg:
1,3,6,1,6,3,5,5,5,4,3,3 will ship for a total of $8.00
will query price db as 3

id 7 will ship for $5 * Quanity + any of the examples above
will query price db as 4

id 8 will ship for $15 * Quantity + any of the examples above
will query price db as 5

table structure for shiprates is
id, price

$temp is currently 6. But the variable result can be changing every time to a different number so it is not a fixed value.

Anyway, for this $temp * 1.1666666, the result will be 6.99999996. Since I used the floor function, it will be rounded down to 6.

Is there any way when the value is more then>*.49999 it will stay at *.5 instead of *?

Example: 6.51111111, 6.78948123, 6.9747124

Expected Output: 6.5

Example: 6.49999999, 6.12412431, 6.33452361

Expected Output: 6

Do note that, $temp value will be ever changing..thank you!

This is the view of the events (basic)  Can I do the if/else statement to chose font color base of a MySQL Query Result? (see below) And
No matter how I have written this, it will not turn into a scrolling table. I want the headers to stay fixed. I also don't want to rely on Java-most of the staff logs in mobile. I will if I have to though!

The other thing I am not seeing how to do, but I know is also possible.... having an "edit event" "view positions" buttons.  I am thinking it has to do with storing a variable, but how do you make sure the variables code pulls the event singularly from your query using the Event_ID ?? 

This is my first site outside of an HTML site.  This is the raw synopsis: 

Building a site that has users with profiles & 6 permission levels.  Client & various staffing levels; I use php to code the access because it is not so much about being able to query/change the database-its more so I am handling with php.  There is a time log, and keeps up with the pay to the staff and what the client owes per their hours.  This is the first layer view once logged in as any staffmember (sans the edit event which is reserved for high perm staff) upon clicking "view positions" they need to be able to request the shift (separate table-please correct me if I am wrong-I have all foreign keys in database in place so making this event table should be along the same as all the others but with diff variables/queries etc. So in theory all I have to do is get the grabbing of the ID field and using it down)

Here is my code. Why is it not setting the client named in green?

<html>
<body>

<?php require_once('auth.php'); ?>


<?php $sql = "SELECT * FROM Events ORDER BY EventDate DESC LIMIT 40";
      $sqlRes = mysql_query($sql);
      $req1 = mysql_num_rows($sqlRes);
      $colorpick = $sqlRes['BusinessName'];?>
<div class="event_view">
<table border="1" cellspacing="2" cellpadding="2" width="100%" height="100%" bgcolor="silver" style="border-collapse:collapse;">
<tr>
<th><font face="Arial, Helvetica, sans-serif" color="black">Event Date</font></th>
<th><font face="Arial, Helvetica, sans-serif" color="black">Event Name</font></th>
<th><font face="Arial, Helvetica, sans-serif" color="black">Start Time</font></th>
<th><font face="Arial, Helvetica, sans-serif" color="black">End Time</font></th>
<th><font face="Arial, Helvetica, sans-serif" color="black">Client Name</font></th>
</tr>

<?php
$i=0;
while ($i < $req1) {

$f1=mysql_result($sqlRes,$i,"EventDate");
$f2=mysql_result($sqlRes,$i,"EventName");
$f3=mysql_result($sqlRes,$i,"Hour");
$f4=mysql_result($sqlRes,$i,"EndHour");
$f5=mysql_result($sqlRes,$i,"BusinessName");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif" color="black"><?php echo $f1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="black"><?php echo $f2; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="black"><?php echo $f3; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif" color="black"><?php echo $f4; ?></font></td>
<td><?php if($colorpick == 'ClientA') { ?>
<font face="Arial, Helvetica, sans-serif" color="green"><?php echo $f5; ?></font>;
<?php } else($colorpick != 'ClientA'); ?>
<font face="Arial, Helvetica, sans-serif" color="black"><?php echo $f5; ?></font></td>
</tr>

<?php
$i++;
}
?>
</table>
</div>
</body>
</html>

I want to get the most recent 15 instances of my a_players_review table THEN sort them by grade, then last name.  Ordering the r.id first just gives me a list of rows that I can't do much else with, unless I'm missing how I can code the output. 

Below is what I had.  ORDER BY looked good until I started filling in more rows and seeing kids in grade = 2021 pushing more recent rows off the list.  

Nested ORDER BY seems frowned upon, and I'm not even sure they work.  I kept getting parameters errors.  I really can't make heads or tails of nested SELECTs.  That might be the answer, but I've been unable to take what I've seen and apply it without getting a parameters error.  

 

 

		$query = "SELECT *,p.id,b.playerID,b.id,s.toggle AS stoggle,o.toggle AS otoggle,p.city,p.school,s.city,s.school,r.opp_city,r.opp_school,o.city,o.school,r.city,r.school 
		FROM a_players_reviews r 
		
		LEFT JOIN a_players p 
			ON CONCAT (r.nameFirst,r.nameLast) = CONCAT (p.nameFirst,p.nameLast)
	
		LEFT JOIN a_schools s 
			ON CONCAT(r.city,r.school) = CONCAT(s.city,s.school)
		LEFT JOIN a_schools o	
		 	ON CONCAT(r.opp_city,r.opp_school) = CONCAT(o.city,o.school)
		
		LEFT JOIN a_player_bookmark b
			ON p.id = b.playerID && '". $userID ."' = b.userID
		
		WHERE p.id = b.playerID && '". $userID ."' = b.userID && bookmark>0
		ORDER BY p.grade,p.nameLast,r.id desc
		LIMIT 15
		";

 

Edited April 7, 2020 by Jim R

Okay,

I would like to redirect my page within a specific time using java script:

<meta http-equiv="Refresh" content="3;url=<?PHP echo 'progress.php?user=';$session->username?>">

Basically, I would like it to redirect to this: progress.php?user=$session->username.

But it redirects to: progress.php?user=

Please help, thanks ...

Hi Guys

I'm sure this is really simple for you experts but it's proving to be my nemesis! I have an array of words: eg.

$words = array('Apples','Bananas','Cherries');

and would like to search a string of text for these words and create link tags around them based on a simple template, <a href="#">$word_found</a>

$text = "I love eating apples and bananas but hate cherries!";

So i would like to return a string like this:

$text = "I love eating <a href=#>apples</a> and <a href="#">bananas</a> but hate <a href="#">cherries!</a>";

Now, I need it to do a case insensitive search, but just wrap tags around the words it finds, leaving the case as it was found in the string. I want to avoid using a function where I have to list all the replacements like this:

function replace_text($text){
    $replace = array('apples' => '<a href="#">apples</a>','bananas' => '<a href="#">bananas</a>');
    $text = str_ireplace(array_keys($replace), $replace, $text);
    return $text;
}

because it seems very messy and long winded. Also it's not case insensitive.

It seems like there must be a way of simply searching for the words in my array and wrapping tags around the found words.

Please can anyone help!?

Many Thanks
Dan

i am trying to do a "file get contents" from the following website
http://www.bet365.com/home/

It is necessary to input your account username and password here, and then I want to get the contents of this page
http://www.bet365.com/extra/en/results-and-archive/results/?Fromdate=13%2f09%2f2014+00%3a00&SearchPath=result&FixtureId=46827110&Period=99&ChallengeId=26278650&CompetitionId=999999&SportId=1&SportDesc=Soccer&Todate=14%2f09%2f2014+00%3a00&LanguageId=1&Zoneid=1

I have tried a couple of scripts but just can't seem to get it to work.

I have tried


$context = stream_context_create(array(
    'http' => array(
        'header'  => "Authorization: Basic " . base64_encode("$username:$password")
    )
));
$data = file_get_contents($url, false, $context);

echo $data;


with the username and password and url entered as variables.
but get the message

Warning: file_get_contents(http://www.bet365.com/extra/en/results-and-archive/results/?Fromdate=13%2f09%2f2014+00%3a00&SearchPath=result&FixtureId=46827110&Period=99&ChallengeId=26278650&CompetitionId=999999&SportId=1&SportDesc=Soccer&Todate=14%2f09%2f2014+00%3a00&LanguageId=1&Zoneid=1) [function.file-get-contents]: failed to open stream: HTTP request failed! in "myscript's address"  on line 13


Maybe i don't need to visit the homepage first to log in?
Any ideas please?
Thank you

apologies for the message being in code quotes, I wrote it in Word but then couldn't paste it, even using the special Word paste button. Hope the message comes out ok. Thank you.
 

I have 5 tables.

1 is which a user specifies another partner id.

The other 4 are different attributes of the partner id, with times for each.

Now what I want to do is pull the data from ALL 4 different tables and then 'mix' them together and order by their respective times (and only choose the values matching the partner id).

I'm getting mighty confused trying left joins, wheres and anything else I know. It seems too complex to be done :s

I'm dealing with data that WordPress creates for Users when they register and subscribe, and to use it for other purposes, I have to wrap my head around serialized data.  It's not sinking in well.  The below code works well for what I wanted at the time. 

It takes into consideration where the Subscriber lives (divided up into five areas) and counts how many I have.  That is noted by wp_s2member_custom_fields and ANY s2member_level.  There are three levels, and I need to actually note how many there are at each Member level. 

There is another line of data for each user_id
meta_key = wp_capabilities
meta_value = a:1:{s:15:"s2member_level2";s:1:"1";}


Code: [Select]

$custom = 'SELECT * FROM wp_usermeta WHERE meta_key = "wp_s2member_custom_fields" AND user_id IN (SELECT user_id FROM wp_usermeta WHERE meta_value LIKE "%s2member_level%")';
$c_results = mysql_query($custom);

$region = array();

while($line = mysql_fetch_assoc($c_results)) {

$meta_value = unserialize($line['meta_value']);

$region[$meta_value['county']]++;
};

foreach ($region as $key => $value) {

echo "Region $key: $value members<br>";
}

I have a Login page which i want to check if the user details (username, password and activation) are valid then redirect user dependent on his/her "accessLevel" (admin, member and none)   Admin will be directed to "index.php" Member will be directed to "tasks.php" and none will be redirected to "notActive.php"   here is my current code which half works;

<?php
include ('connect.php');
if(isset($_POST['submit'])) {
    // Initialize a session:
session_start();

// Define $username and $password 
$username=$_POST['username']; 
$password=$_POST['password']; 

// To protect MySQL injection
$username = stripslashes($username);
$password = stripslashes($password);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
$sql="SELECT * FROM ecmt_members WHERE username='$username' and password='$password' AND Activation IS NULL";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $username and $password, table row must be 1 row
if($count==1){

// Register $username, $password and redirect to file "index.php"
$role = mysql_fetch_array($result);
$_SESSION['username']= $username; 
$_SESSION['password']= $password;
//$_SESSION['role']= $role['accessLevel'];
 
 if($role['accessLevel'] == "admin"){
	 $_SESSION['adminuser']=$role['accessLevel'];
		header("location:index.php");
			exit();
      }
 elseif($role['accessLevel'] == "member"){
	 $_SESSION['user']=$role['accessLevel'];
		header("location:tasks.php");
			exit();
     }

else {
echo "Error: Username, Password or Access Level incorrect! Go Home, you're Drunk!!!";
}

	}
    } // End of the main Submit conditional.
?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Login Form</title>

<!-- jQUERY -->
<script src="//code.jquery.com/jquery-latest.js"></script>
<!-- Add Main CSS -->
<link rel="stylesheet" type="text/css" href="../tool/css/main.css">
<!-- Font-Awesome -->
<link href="//maxcdn.bootstrapcdn.com/font-awesome/4.2.0/css/font-awesome.min.css" rel="stylesheet">
	<style>
		body { 
			background: url(../tool/images/bg1.jpg) no-repeat center center fixed; 
			-webkit-background-size: cover;
			-moz-background-size: cover;
			-o-background-size: cover;
			background-size: cover;
		}
.exactCenter { width:auto; height:auto; position: fixed; top: 50%; left: 50%; margin-top: -200px; ; }

	</style>
</head>
<body>
<div class="exactCenter">
<div id="login-container">
<img src="../tool/images/bigLogo.png" /><br />

<form action="login.php" method="post" class="form">
  <fieldset class="group">
      <input class="input" type="text" id="username" name="username" size="25" placeholder="username"/>
<img src="../tool/images/spacer.png" width="5" height="5" />
      <input class="input" type="password" id="password" name="password" size="25" placeholder="password"/>
<img src="../tool/images/spacer.png" width="5" height="5" />
     </fieldset>    
      <input type="hidden" name="formsubmitted" value="TRUE" />
      <button type="submit" name="submit" class="btn btn-blue" ><i class="fa fa-unlock-alt"></i> Login</button>
<img src="../tool/images/spacer.png" width="5" height="5" />   
<a href="Register.php"><button type="button" class="btn btn-green" ><i class="fa fa-hand-o-up"></i> Register</button></a>
</form>

</div></div>
</body>
</html>

When i test it with admin login credentials i get the page refresh again without error, when i login with member credentials it shows tasks.php and when i try to log in with unactivated account "acivation column is not NULL" i get a page refresh also with no error.   Can someone help me make it work for each role and perhaps have some sort of error reporting depending on error.   im pulling my hair out here and this is the first time i have worked with login conditions, and im VERY confused.
Edited by jacko_162, 28 October 2014 - 05:12 PM.

For this example, $row2['author'] = "1"
$user = 'user'.$row2['author'];
if (!isset($$user)){
      $result = $connector->query('SELECT * FROM users WHERE ID = '.$row2['author']);
      $$user = $connector->fetchArray($result);
}
echo '<a href="user.php?user='.($$user['ID']).'">'.$$user.'</a></p>';
The problem is it is a weird variable that also has an array, but when i use
echo $$user['ID'];
It errors out saying Notice: Quote

Undefined variable: u


So I am wondering how to word it to set the variable up properly, and how to call items in the array. Thanks in advance

I am trying to use this: http://php.net/manual/en/function.xml-parse-into-struct.php to parse this:

Code: [Select]
<?xml version="1.0" encoding="utf-8"?>
<PaymentNotification xmlns="http://">
  <PaymentMethod>card</PaymentMethod>
<Hash>12345KKLS</Hash>
<Payments>
    <Payment>
      <PaymentID>709750ba-b9b6-44c4-9812</PaymentID>
      <Amount>70</Amount>
      <Currency>USD</Currency>
      <Date>2011-05-11T07:41:44.957</Date>
      <StatusID>4</StatusID>
    </Payment>
    <Payment>
      <PaymentID>e0c66bae-c423-43c6-b896</PaymentID>
      <Amount>50</Amount>
      <Currency>USD</Currency>
      <Date>2011-05-11T07:42:13.55</Date>
      <StatusID>2</StatusID>
    </Payment>
  </Payments>
</PaymentNotification>

...but can't work out which numbers to use. I only need to grab the payment ID where the statusID is 4 (for each one as could be 1 or more per xml message).

It's very complicated http://php.net/manual/en/function.xml-parse-into-struct.php