Posts by urtrivedi

This is most complex query. I hope this is what you are looking for.

select a.id, a.username, a.id_status,sum(a.won) won, sum(a.void) void, sum(a.lost) lost

(

select Users.id, Users.Username, User_status.id_status, 
case when Analysis.status_name='Won' then 1 else 0 end won,
case when Analysis.status_name='Void' then 1 else 0 end void,
case when Analysis.status_name='Lost' then 1 else 0 end lost,
@row := case when @prevuser=Users.Username then @row + 1 else 1 end as rownum,
@prevuser:=Users.Username
FROM Users,(SELECT @row := 0, @prevuser:=null ) r  
INNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
order by Users.Username, Analysis.Tipster date

) a
where a.rownum <=20
GROUP BY a.id, a.Username, a.id_status

Ya you may also use code posted by vibhadevit.

$condition="";

if(isset($_POST['bedrooms']) && isset($_POST['bathrooms']) )
    $condition=" where bedrooms like '{$_POST['bedrooms']}' AND bathrooms like '{$_POST['bathrooms']}' "; 
elseif(!isset($_POST['bedrooms']) && isset($_POST['bathrooms']) )
    $condition=" where bathrooms like  '{$_POST['bathrooms']}' "; 
elseif(isset($_POST['bedrooms']) && !isset($_POST['bathrooms']) )
    $condition=" where bedrooms like '{$_POST['bedrooms']}' "; 


$query= "SELECT * FROM inventory {$condition} ORDER BY model_name ASC";

You play with where clause of last query i have given. I think you will get it.

I am not able to understand what you are trying to tell.
You simply show sample data, you have and show me the expected output from it.

select Users.id, Users.Username, User_status.id_status,
sum(case when Analysis.status_name='w' then 1 else 0 end) won,
sum(case when Analysis.status_name='v' then 1 else 0 end) void,
sum(case when Analysis.status_name='l' then 1 else 0 end) lost
FROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Users.id, Users.Username, User_status.id_status
order by won desc

i removed having (won+void+lost)<20. you where asking for limiting it thats why i place that conidtion.

SELECT Users.id, Users.Username, User_status.id_status, 
sum(case when Analysis.status_name='w' then 1 else 0 end) won,
sum(case when Analysis.status_name='v' then 1 else 0 end) void,
sum(case when Analysis.status_name='l' then 1 else 0 end) lost
FROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_status
left outer JOIN Analysis ON Users.Username=Analysis.Tipster
WHERE User_status.id_status>=3
GROUP BY Users.id, Users.Username, User_status.id_status
having (won+void+lost)<=20

your query could be like following

SELECT DISTINCT vehicles.vehicles_model, manufacturer.manufacturer_name from ....

For option element use vehicle_model field, do not use vehicle_id for option value.

echo "<option value=$nt[vehicles_model]>Model: $nt[vehicles_model] Year: $nt[vehicles_year]</option>";

post you tables structure with sample data. or attach such file

Also tell you want vehcile_id or vehicle_model in next page.

you may user master table only for listing vechicle id, Here you are joining so many tables, that is creating problem for you.

And I think i suggested you to remove year from the query.

i think there is a problem in vehicle_id of you query. in first page. it is null,
YOu have selected vehicles_id column in your query. please check it.

Your code seems to be fine, I am not able to trace the problem.

just again try at the begining of you destionation page, SEE WHAT COMES.

echo "<pre>";
print_r($_POST);
echo "</pre>";

You remove line

echo"<input type=\"hidden\" name=\"vehicles_model\" value=\"$model\">";

becuase you alreay have <select name=vehicle_model> no need to have hidden field for it.

Please check the spelling of vehicle-model (that you hightligted in red). Use underscore(_) instead of hypen(-).

use ifnull function around profit column.

Also change line 13 to
$sel_profit = $row;

"SELECT SUM(ifnull(Analysis.Profit,0)) profit, Users.id, Users.Country_name, Users.Username, User_status.id_statusFROM UsersINNER JOIN User_status ON Users.User_status_name=User_status.user_statusleft outer JOIN Analysis ON Users.Username=Analysis.TipsterWHERE User_status.id_status>=3GROUP BY TipsterORDER BY SUM(ifnull(Analysis.Profit,0)) DESC"

which query is not working properly first or second.

or

why you have used two quries

at the end of query write

order by profit desc

when you run first page just view html source. check whether all hidden fields are set properly, are they getting values or not.

secondly. when you open destination page. what values you are missing or exactly what problem occur.

write following code in destionantion file, to check whether data is posted or not

echo "<pre>";
print_r($_POST);
echo "</pre>";

My fisrt script was better. it shows all files in your directory in file order. you may user a.txt... like that.

another version given below based on 1,2,3,4 series, which depends on series of number. if suppose 5.txt is not there then below script will fail.

$currdirectory=".";//path or your current directory from where you want to show files. i assume all files are text files
	if($_REQUEST['txtfile']=="")
		$txtfile=$currdirectory."/1".".txt";
	else
		$txtfile=$currdirectory."/".$_REQUEST['txtfile'].".txt";
	

	if(file_exists($currdirectory."/".($_REQUEST['txtfile']-1).".txt"))
		$prevfile=$_REQUEST['txtfile']-1;
	if(file_exists($currdirectory."/".($_REQUEST['txtfile']+1).".txt"))
		$nextfile=$_REQUEST['txtfile']+1;
	

	$handle=fopen($txtfile,"r");

	$contents=fread($handle,filesize($txtfile));
	echo($contents);
	fclose($handle);
	echo "<br>";
	if($prevfile!="" )
		print "<a class='text17' href='{$_SERVER['PHP_SELF']}?txtfile={$prevfile}'>Previous</a>&nbsp;&nbsp;";
	if($nextfile!="")
		print "&nbsp;<a class='text17' href='{$_SERVER['PHP_SELF']}?txtfile={$nextfile}'>Next</a>";

its your choice what to use

it depends on order in array so its sorts in text mode and not in numeric mode.

Use following script, nothing more to do. Set your text directory path in currdirectory folder.

<?php
	$currdirectory="textfilesfolder";//path or your current directory from where you want to show files. i assume all files are text files
	if($_REQUEST['txtfile']=="")
		$txtfile=$currdirectory."/1.txt";
	else
		$txtfile=$currdirectory."/".$_REQUEST['txtfile'];
	
	
	$files = scandir($currdirectory);				
	$nextfile=$files[2];
	for ($i=0;$i<count($files);$i++)
	{
		$content=$currdirectory."/".$files[$i];
	 	if (is_file($content))
	 	{
	 	 	if($_REQUEST['txtfile']==$files[$i])
	 	 	{
				$prevfile=$files[$i-1];
				//$txtfile=$files[$i];
				$nextfile=$files[$i+1];
				break;
			}
		}

	}
	
	$handle=fopen($txtfile,"r");
	$contents=fread($handle,filesize($txtfile));
	echo($contents);
	fclose($handle);

	if($prevfile!="" && $i>=3)
		print "<br><a class='text17' href='{$_SERVER['PHP_SELF']}?txtfile={$prevfile}'>Previous</a>&nbsp;&nbsp;";
	if($nextfile!="")
		print "&nbsp;<a class='text17' href='{$_SERVER['PHP_SELF']}?txtfile={$nextfile}'>Next</a>";
 
         ?>

It is used to suppress errors. Even if function fails, it continues execution. But we should avoid using it, let the error shown and fix it.

You may also use javascript datepicker library. In that case you need not to use seperate selection boxes

at line no 12 write following code

$page=$_REQUEST['page'];

specify your problem in detail may be with your code.

remove
, vehicles.vehicles_year
from your query.

Following query will add 15 minutes to col2 and then compare new time with col1.
col1 and col2 must be datetime type column.

select * from mytable 
where col_1_datetime= date_add(col_2_datetime ,interval 15 minute)

Is your problem solved? if not then please specify the problem or close this thread.

You simple cut your line 27

echo "<form action=\"cat.php\" method=\"post\">\n";

and place it at line no 8 that is before executing your query. your select-option must be the part of your form to post it to next page.

you may write update code at the end of your fist database connection file.

do not Write code in every page.

It is alias for timestampdiff(.....) column.
if your user table is like following

useriid, last_accesssed
urtrivedi, 2010-06-03 12:10:00
ayesha, 2010-06-03 11:40:00

now if you execute following query at say 12:30

select TIMESTAMPDIFF(second,last_accessed,current_timestamp) login_since_minutes from usertable

then execution will give

userid, login_since_minutes
urtrivedi, 20
ayesha, 50

means urtriedi visited some page before 20 minutes and ayesha visited some page before 50 minutes

following query will give the username with minutes(page accessed by user before that much minutes). You may set one standard say 20 minutes. If minutes is less than 20 minutes then he/she is online if minutes is greater than 20 then user is offline.

select userid, TIMESTAMPDIFF(minute,last_accessed,current_timestamp) login_since_minutes
 from usertable

As almostbob suggested you may Use your menu file if you are having or any other file that is called in almost all pages to update user status.

I would prefer to do it in following way. I am not sure that it is the best way to do it or not. But it is just to start with.
1) create column in user master say last_accessed (datetime)
2) in you common file you may update user last_accessed column
suppose you store user name in session then you may write update query at the end of your file.

update usertable set last_accessed=current_timestamp() where userid='{$_SESSION['userid]}'

3) now you have time for each user in your usertable. now you may use this column for reporting, that depends on your requirements.

Are you getting?

Is there a page which is called from all pages, some configuration or connection page. You may use that page to update user table. And I suggest to insert timestamp intead of flag.

I think you have not intialized $page anywhere in the code. so line number 9 always sets $page to 1.

I guess name of textbox is userinput. so when you submit you form for processing in processing page you can trim the leading zeros by casting it.

<?php
	$newvalue=(int)$_REQUEST['userinput'];
?>

I guess you are asking for php solution as you have posted it in php forum

cast your value using (int).

<?php
	$newvalue=(int)$_REQUEST['userinput'];
?>

you are doing it in reverse order, following is the correct version

$sql="UPDATE $tbl_name set password='$new_password' WHERE user='$user'";

if you still want it in middle then you must set width to 100%

<style="width:100%">

<html>
<head></head>
<body>

<div id="container" >
    <div id="header" align="center">Header bits go here</div>
    <div id="content" align="center">Content bits go here
    <div id="footer" align="center">Footer bits go here</div>
</div>
</body>
</html>

SELECT Analysis.Profit, Users.Username, User_status.id 
	FROM Users INNER JOIN User.status ON Users.User_status_name=User_status.user_status 
	left outer join Analysis on Users.Username=Analysis.Tipster 
	WHERE  User_status.id>=3
ORDER BY Profit

Please be specific about your problem. What problem you are facing? And why you have embedded html code in your query code.

I think there is problem with the relation between the tables. If you want to join analysis table with users table then there must be some common column between them. but in analysis table there is no column related to users.

So have some link between these two tables and join on the basis of this columns in your query. Also you may use aggregate functions like sum, average for getting your result and to remove redundant rows from query result.

When you are opening whole file in textarea then why you are treating it as csv, simply load text area with content, modify and save. I hope following code will help you

<form action="save.php" method="post">
<textarea name="events" cols="100" rows="10" style="text-align:left">
<?php 
	$handle=fopen("events.csv","r");
	echo fread($handle,filesize("events.csv"));
	fclose($handle);

?>
</textarea><br />
<input type="submit" name="Edit" value="Save" />
</form>

I am writing code for your third page.

In php you must use post element name without [], to access whole array

Checked item ids will be displayed by the following code

<?php

echo "close now checked <br>";
for($i=0;$i<count($_POST['closenow'];$i++)
{
	echo "{$_POST['closenow'][$i]}<br>";
}

echo "delete checked <br>";
for($i=0;$i<count($_POST['O_delete'];$i++)
{
	echo "{$_POST['O_delete'][$i]}<br>";
}
?>

you again use do ....while () loop
that will solve your problem. because your code is desinged that way.

at line 8 add
echo $query_getPages;
copy the output query from browser and run in phpmyadmin. check the result

First of all let me know Have you applied the suggested changes and are you getting the grand total.

Secondly If you not getting more recrods then, You should post the portion of your code where you are executing database query.
supppost your code is like below, You just echo your query text, copy it and run it in phpmyadmin or other database tool, and see whether it is working there fine.

$query="select * from mytable where mycol=mycondtion";
echo $query;
.
.
.
$result=mysql_query($query");
.
.

I am not sure what is your problem, even though I guessed your problem and made certain changes.
1) used while{ } loop instead of do.. while loop
2) used two variable grandnaira & granddollar , intialize them and added current value in every iteration.
3) You were printing grand total in the while loop, i m not sure why. so I have taken out that grand total row out of while loop and printed two varaibles grandnaira & granddollar at appropriate place. I have also remove html tr ID for the grand total row.

</table><table width="95%" border="1" align="center" CELLPADDING="2" CELLSPACING="0" bordercolor="<?PHP print "$BorderColour"; ?>">
          <tr valign="middle" class="tableheadsx">
            <td width="91" height="29" align="left" class="welcomexx"><strong>Date</strong></td>
            <td width="136" height="29" align="left" class="welcomexx"><strong>Distributor's ID </strong></td>
			<td width="136" height="29" align="left" class="welcomexx"><strong>Customer's Name </strong></td>
            <td width="140" height="29" align="left" class="welcomexx"><strong>Total Paid(Naira) </strong></td>
			<td width="157" height="29" align="left" class="welcomexx"><strong>Total Paid(Dollar)</strong></td>
          </tr>
		  <?php 
		  $rowShine = 1;
		 //next 2 lines added by urtrivedi,			 		  
		  $grandnaira=0;
		  $granddollar=0;
		  ?>
          <?php if ($totalRows_getPages > 0) { // Show if recordset not empty ?>
            <?php  while ($row_getPages = mysql_fetch_assoc($getPages)) { ?>
              <tr id="row-<?php echo $row_getPages['code']; ?>" <?php if($rowShine%2){
						echo "class=\"alternate\" ";
					}
			?>>
			<?php 
			
			 $me=$row_getPages['totalDollar']; 
			 $mee=$row_getPages['totalNaira']; 
			 //next 2 lines added by urtrivedi,			 
			 $grandnaira +=$mee;
			 $granddollar+=$me;
			 
			 ?>
                <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['xdate']; ?></td>
				 <td height="25" align="left" valign="top" class="style1"><?php echo $row_getPages['dis_id']; ?></td>
                <td height="25" align="left" valign="top" class="style1"><strong><?php echo $row_getPages['dis_name']; ?></strong></td>
                <td height="25" align="left" valign="top" class="style1"><?php print number_format($mee,2); ?></td>
				<td height="25" align="left" valign="top" class="style1"><?php print number_format($me); ?></td>
              </tr>
              
              <?php $rowShine++;?>
              <?php }//end while   ?>
			
			
			
			
			
			
			
			<tr …

I am giving you every code segment after proper testing. I dont know why its not working there. any ways post your final code for others.

Have you changed the code as I mentioned