PHP - Complicated (for Me) Order By Issue...

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.

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.

$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!

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

I have this kind of query:

SELECT db1.field, db2.field FROM table1 db1 INNER JOIN table2 db2 ON db1.matchingField=db2.matchingField INNER JOIN table3 db3 ON db2.matchingField=db3.matchingField

(table 3 is used as there are various other WHERE clauses etc.)

and need to add in so it pulls info from a 4th table

SELECT db1.field FROM table1 db1 UNION ALL SELECT db4.newField FROM table4

How would I merge the two queries?

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

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 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 have a stored procedure on MSSQL that returns a list of results.  This works fine.  The results that are returned may or may not be allowed to be seen by the end user.  So, if 10 results are returned, maybe 7 of them are allowed to be viewed by the end user.  There is a PHP SESSION variable that contains a bunch of codes that is compared to a variable returned from the search result. When these match, the result can be seen by the user.

So, I am using a php if statement that echos the result based on the result of the if statement.  That works fine.  What does not work  is the found count for the array.  For example, if 10 are found, it returns 10, but the if statement removes 3 making 7 of the results viewable. 

So, is there a way to iterate through an array result set in PHP and remove results based on an if statement creating a new array?

Hope this is not too confusing! 

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.

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

Hello

I am busy trying to setup a free to use budgeting and cashflow tool.  I was recently going through my finances and whilst I found lots of spreadsheets on the subject, I didn't find a website that would allow me to store my data anonymously and access it when and where I felt fit.

Bare in mind that I am a bit of a novice so go easy on me...

So firstly, I've setup a page to collect 'account' information...that is, income items and expense items. I ask them to enter the amount, the frequency and when the next due date is.
The DB looks like this:
Code: [Select]
CREATE TABLE IF NOT EXISTS `income_accounts` (
  `inc_acc_ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` int(11) NOT NULL,
  `freq_ID` int(11) NOT NULL,
  `amount` decimal(7,2) NOT NULL,
  `next_due` date NOT NULL,
  `income_typeID` int(11) NOT NULL,
  PRIMARY KEY (`inc_acc_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=105 ;
and the same for expense 'accounts':
Code: [Select]
CREATE TABLE IF NOT EXISTS `expense_accounts` (
  `exp_acc_ID` int(11) NOT NULL AUTO_INCREMENT,
  `user_ID` int(11) NOT NULL,
  `freq_ID` int(11) NOT NULL,
  `amount` decimal(7,2) NOT NULL,
  `next_due` date NOT NULL,
  `ex_typeID` int(11) NOT NULL,
  PRIMARY KEY (`exp_acc_ID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
So once I have these entered by the user I should be able to then generate a cashflow forecast.
For example, I give 5 options for the user: '4 weeks' , '8 weeks',  '3 months',  '6 months'  '12 months'. Selecting one of these will then generate a table listing all of the items that apply for each period with a total at the bottom.
Lets take '4 weeks' for an example.
First I need to determine if the 'next_due' value is within the time period selected (4 weeks).  If it is, what frequency is it? If it is every week then each column of the 4 column table created (i.e. 4 weeks/4 columns) will have it in it.  If it is monthly frequency it will be only in the column for which it is due (i.e. column 1 would be todays date to todays date +6, column 2 = todays date + 7 to todays date +13 etc...)
This will go through both the income table and the expense table and once all the items have been checked and added to the page table a further row would be added to total all the items in each column, and give a sum (i.e. sum of incomes less sum of expenses).
For the '6 month' and '12 month' option, I want to display 1 month per column, rather than 1 week...just to not squeeze things up to much...

I do not have a great deal of experience with arrays but my instinct tells me that they are the answer....otherwise I guess I could create a temporary DB table?

I'm happy to share the code I've written so far if it helps...otherwise any advice on the best way to achieve this would be great.