PHP - How To Show The Option Based On The Value In The Table?

Hi All ,   I have a small table with 4 fields namely Day_ID, Dues, Last_Visit, Points. where Day_ID is an auto-increment field. The table would be as follows:   Day_ID -- Dues ---  Last_Visit ---  Points. 1  --------- 900 -------- 1/12  --------   6      2  --------- 700 -------- 4/12  --------   7 3  --------- 600 -------- 7/12  --------   5 4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   So this is the record of a person's visit to say a club. The last row indicates the last date of his visit to the club. His points on this date are 6. Based on this point value of 6 in the last row I want to retrieve all the previous BUT adjoining all records that have the same Points i.e. 6.    So my query should retrieve for me, based on the column value of Points of the last row (i.e. Day_ID - 6 ), as follows:   4  --------- 600 -------- 9/12  --------   6 5  --------- 600 -------- 10/12 -------   6 6  --------- 600 -------- 14/12 -------   6   This problem stated above had been completely resolved, thanks to a lot of help from Guru Barand by this following query :-

$query = "SELECT cv.day_id, cv.dues, cv.last_visit, cv.points
FROM clubvisit cv
WHERE last_visit >=
(
SELECT MAX(last_visit) FROM clubvisit
WHERE points <>
(
SELECT points as lastpoints
FROM clubvisit
JOIN
(
SELECT MAX(last_visit) as last_visit
FROM clubvisit
) as latest USING (last_visit)
)
)";

I am using this and it works perfectly except that now there is a slight change in the table because the criteria for points is now dependent on more than one column cv.points and is more like cv.points1, cv.points2, cv.points3 etc. So now I need to make a selection based on each of these cv.points columns.   As of now I can still get the results by running the query multiple times for each of the cv.points columns ( seperately for cv.points1, cv.points2, cv.points3) and it works correctly. However I am wondering if there is a better way to do this in just one go. This not only makes the code repetitive but also since the queries are interconnected, involves the use of transactions which I wish to avoid if possible.    The values that I require for each of the cv.point columns is  1. day_id of the previous / old day on which the cv.points value changed from the current day value, and  2. cv.points on that old/ previous day.    So for  example if the table is as below:   Day_ID -- Dues ---  Last_Visit ---  Points1 --- Points2. 1  --------- 900 -------- 1/12  -----------   9  ------------ 5    2  --------- 600 -------- 4/12  -----------   6  ------------ 6 3  --------- 400 -------- 7/12  -----------   4  ------------ 7 4  --------- 500 -------- 9/12  -----------   5  ------------ 8 5  --------- 600 -------- 10/12 ----------   6  ------------ 8 6  --------- 600 -------- 11/12 ----------   6  ------------ 8 7  --------- 600 -------- 13/12 ----------   6  ------------ 7 8  --------- 500 -------- 15/12 ----------   5  ------------ 7 9  --------- 500 -------- 19/12 ----------   5  ------------ 7   Then I need the following set of values : 1. day_id1 -- Day 7,      points1 ---- 6,          days_diff1 -- (9-7 = 2)  . // Difference between the latest day and day_id1 2. day_id2 -- Day 6,      points2 ---- 8,          days_diff2  -- (9-6 = 3) 3. day_id3 -- ....   and so on for other points.    Thanks all !

Hello,   I want to show checkbox is checked when there is entry of that id in a table in my database.    I have 2 tables page and access_level. I am getting data from page table and displays it in <ul><li> tag with checkbox to select all or only few. After selecting the checkbox, i will store only selected checkbox value in access_level table along with table id. Page link and page name details will be stored in page table.    Now if i want to edit , i should display all the pages which is there in page table and i should also mark checked to those which are already stored in access_level table.   I am using LEFT OUT JOIN, It displays all the pages. But it is not displaying the check mark to those which are already selected.   Here is my code

<?php $s1 = mysql_query("SELECT pages.page_id, pages.code, pages.page, pages.href, access_level.aid, access_level.page_id as pgid, access_level.department, access_level.position, access_level.active FROM pages LEFT OUTER JOIN access_level ON pages.page_id=access_level.page_id WHERE access_level.department=".$department." AND access_level.position=".$position." AND pages.code='sn'") or die(mysql_error());
				while($s2 = mysql_fetch_array($s1)) { ?>
				<tr><td><li><?php echo $s2['page']; ?>  </td><td><input type="checkbox" name="sn[]" value="<?php echo $s2['page_id']; ?>"  
                <?php if($row['pgid'] === $s2['page_id']) echo 'checked="checked"';?> />

here is my pages table     pages.JPG   26.55KB   0 downloads   access_level  access_level.JPG   19.09KB   0 downloads   In access_level table i do not have page ids 8 and 9. But i want to display that also from pages table and for 1 to 7 and 10 i should display check mark.    How i can achieve this? Please Help

I have made a data table out of my mySQL table and I should have a search option.
Lik where you have 3 boxes on one line :
Naam - Date1 - Date2
Naam is just searching results with that value
You can insert Date1 and Date 2 and then it must search between those dates.
But what will it do if i only put 1 date ? Like 2011-12-23 and date2 open
will it then only show the dates from 2011-12-23 ?   <html><body><?php$username="";$password="";$database="";mysql_connect(localhost,$username,$password);@mysql_select_db($database) or die( "Unable to select database");$query = "SELECT middenoosten2.*, users.naam ". "FROM middenoosten2, users ". "WHERE middenoosten2.speler = users.inlog order by id asc";$result=mysql_query($query);$num=mysql_numrows($result);mysql_close();?><p><b>Scores middenoosten : Zee en rivieren</b></p><table border="1" cellspacing="2" cellpadding="2"><tr><th><font face="Arial, Helvetica, sans-serif">Id</font></th><th><font face="Arial, Helvetica, sans-serif">Naam</font></th><th><font face="Arial, Helvetica, sans-serif">Score 11/...</font></th><th><font face="Arial, Helvetica, sans-serif">datum</font></th><th><font face="Arial, Helvetica, sans-serif">uur</font></th><th><font face="Arial, Helvetica, sans-serif">percent</font></th><th><font face="Arial, Helvetica, sans-serif">duur</font></th></tr><?php$i=0;while ($i < $num) {$f1=mysql_result($result,$i,"id");$f2=mysql_result($result,$i,"naam");$f3=mysql_result($result,$i,"score");$f4=mysql_result($result,$i,"datum");$f5=mysql_result($result,$i,"tijd");$f6=mysql_result($result,$i,"percent");$f7=mysql_result($result,$i,"crono");?><tr><td><font face="Arial, Helvetica, sans-serif"><?php echo $f1; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f2; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f3; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f4; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f5; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f6; ?></font></td><td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td></tr><?php$i++;}?></body></html>()

Thank you

Hello I want product filter groups by category.

There will be different options.

Ex: http://prntscr.com/sr00ay

Product Table : http://prntscr.com/sr00rm

Option Groups Table : http://prntscr.com/sr013r

Options Table : http://prntscr.com/sr01fk

Product Option Table : http://prntscr.com/sr01r3

How can I build this structure? And how should it be sql for filtering? Is the structure correct? I'm waiting for your help.

Edited May 31, 2020 by Atakan

This is a little confusing so please bear with me.  I was thinking of using left.join but couldn't figure out how to implement it properly.

I am trying to order all of the results from a table names 'clans' based on how many points the clan has. To calculate the points you have to go into another table 'clanteams' and then loop every team in the clan pulling wins and losses from a row in a specific ladder table.

Here is the code i have to calculate the points.

Code: [Select]
$total[wins] = 0;
$total[loss] = 0;
$members=mysql_query("SELECT id,clanid,teamid,DATE_FORMAT(datejoined,'%M %d, %Y') FROM clanteams WHERE clanid='$member[id]'");
while(list($id,$clanid,$teamid,$joined)=mysql_fetch_row($members)){
$team=mysql_query("SELECT name,ladderid FROM teams WHERE id='$teamid'");
if(mysql_num_rows($team) == 0) continue;
$team=mysql_fetch_array($team);
$ladder=mysql_query("SELECT name FROM ladders WHERE id='$team[ladderid]'");
$ladder=mysql_fetch_array($ladder);
$linfo=mysql_query("SELECT rank,wins,loss FROM ladder_$team[ladderid] WHERE teamid='$teamid'");
$linfo=mysql_fetch_array($linfo);

$total[wins] = $total[wins] + $linfo[wins];
$total[loss] = $total[loss] + $linfo[loss];

}
$totalpoints = ($total[wins] * 2) - $total[loss];

So now i want to loop through every row in the clans table, and using the above code oder them by $total points.
Ive spent hours wrapping my head around it and still cannot figure it out.

Please help.

I'm wondering how to do this with javascript for a table that i have made in php and html.

I want to hide the row for the table based on the value inside my first column for each row of the table.

Like if column 1 has "apple" inside it, then the row would not be shown.
I'm pretty sure this can be done in javascript but i'm not a coding guy, so any help would be appreciated!

My table.php file that has the table i want to do this for:
Code: [Select]
<script language="javascript">

imageX1='plus';
imageX2='plus';
imageX3='plus';

function toggleDisplay(e){
imgX="imagePM"+e;
tableX="table"+e;
imageX="imageX"+e;
tableLink="tableHref"+e;
imageXval=eval("imageX"+e);
element = document.getElementById(tableX).style;
 if (element.display=='none') {element.display='block';}
 else {element.display='none';}
 if (imageXval=='plus') {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='minus';");document.getElementById(tableLink).title='Hide WoW';}
 else {document.getElementById(imgX).src='http://www.pvpstreams.com/icons/wowicon.png';eval("imageX"+e+"='plus';");document.getElementById(tableLink).title='Show WoW';}
}
</script>

<?php
$username="user";
$password="password";
$tmp="database";

mysql_connect(localhost,$username,$password);
@mysql_select_db($tmp) or die( "Unable to select database");
$query="SELECT * FROM users";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();
?>
<table class="sortable" id="wow" border="1" cellpadding="0" cellspacing="0" style="margin-left:auto;margin-right:auto;text-align:left">
<tr>
<th><font face="Arial, Helvetica, sans-serif">Game</font></th>
<th><font face="Arial, Helvetica, sans-serif">Character</font></th>
<th><font face="Arial, Helvetica, sans-serif">Rating</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png">&nbsp;Status</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/xfirelogo.png">&nbsp;Link</font></th>
<th><font face="Arial, Helvetica, sans-serif">Realm</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png">&nbsp;Status</font></th>
<th><font face="Arial, Helvetica, sans-serif"><img src="http://www.pvpstreams.com/table-images/livestreamlogo.png">&nbsp;Link</font></th>
<th><font face="Arial, Helvetica, sans-serif">Team&nbsp;Setup</font></th>
</tr>

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

$f1=mysql_result($result,$i,"wowcharacter");
$f2=mysql_result($result,$i,"username");
$f3=mysql_result($result,$i,"livestream");
$f4=mysql_result($result,$i,"wowserver");

$f5=mysql_result($result,$i,"wowplayer1");
$f6=mysql_result($result,$i,"wowplayer2");
$f9=mysql_result($result,$i,"wowplayer3");

$f7=mysql_result($result,$i,"wowrealm");
$f8=mysql_result($result,$i,"xfire");
$f10=mysql_result($result,$i,"xfirecheck");
$f11=mysql_result($result,$i,"wowrating");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<center><img src=http://www.pvpstreams.com/table-images/wowtable.png>"; ?></center></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://$f4.battle.net/wow/en/character/$f7/$f1/simple>$f1</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f11; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "$f10"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/xfire.php?user=$f2>$f8</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo $f7; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php include("livestreamcheck.php"); ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<a class=blue href=http://www.pvpstreams.com/livestream.php?user=$f2>$f3</a>"; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><?php echo "<img src=http://www.pvpstreams.com/wclassicons/$f5.png><img src=http://www.pvpstreams.com/wclassicons/$f6.png><img src=http://www.pvpstreams.com/wclassicons/$f9.png>" ?></font></td>
</tr>


<?php
$i++;
}
?>
</table>

how to format only one specific row based on the value of  one cell in a row?

$result = mysql_query(select bal,  etc. etc ..query )

while($row = mysql_fetch_assoc($result))

echo "<tr>";

echo "<td>" . $row['Account'] . "</td>";

 echo "<td>" . $row['licence'] . "</td>";

 echo "<td>" . $row['own'] . "</td>";

 echo "<td>" . $row['bal'] . "</td>";

  echo "</tr>";

Would appreciate help on how to format only the $row['bal'] output to red colour IF bal >5000

Thanks

This topic has been moved to JavaScript Help.

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

Dear All

I want to display my data in tabular columns with the headings of table  like 00:06-00:08,00:08-00:10,00:10:00:12:00........22:00-24:00.i.e.time
In database I have two columns one containing value and other containing time when this reading was done.
I could retrieve data from my mysql table in php and look at time.
I want to know how could I insert the value to the correct column of table based on the time.
e.g If I have record in table in database say containing value 09:45 and 5.
I want this value to go to the column heading 00:08-00:10 and same thing for the other values.

I hope I am clear with the problem definition but if you need more info.Please let me know.

Thanks,
elp

hi 

I have 2 tables (members & friendship)



Members table columns:
--------------------------------------------
username | password |    email    | etc ....
--------------------------------------------
Person 1     xxxxxx        x@x.com
person 2     yyyyyy        y@y.com
 
Friendship columns:

---------------------------------------
username |   friend   |  status  |
---------------------------------------
person 1     person 2     friends
person 2     person 1     friends
person 1     person 3     pending



I want to query the friendship table: "SELECT friend FROM friendship WHERE username ='$_SESSION['myusername'];'  AND status='friends' "

(so its basically getting the user names of anyone who is the logged in users friend)

And then use the returned user names  to select their data from the members table.

Any help much appreciated!

^.^
 

I have 3 tables w/ the following fields:

Cities
id | state_id | city_name

States
id | state_name

New_cities
id | state_id | city_name

I am trying to write a script (and still working on it) that will INSERT new cities in cities WHERE New_cities.state_id = Cities.state_id.

Obviously I am still in testing and results sort out, but honest to all hell, I might be going about this all wrong, and would appreciate any comments, criticism, assistance or feedback on what I am trying to do.

<?php
  $query = "SELECT * FROM cities, states, new_cities";
  if ($results = mysqli_query($cxn, $query)) {
      $row_cnt = mysqli_num_rows($results);
      echo $row_cnt . " Total Records in Query.<br /><br />";
      if (mysqli_num_rows($results)) {
          while ($row = mysqli_fetch_array($results)) {
              if ($row['cities']['state_id'] == $row['new_cities']['new_state_id']) {
                  $insert_city_query = "INSERT INTO cities id, new_state_id, city_name VALUES
('','$new_state_id','$city_name')" or mysqli_error();
              } //if ($row['cities']['state_id'] == $row['new_cities']['new_state_id'])
              echo "$row[new_city_name]<br />";
          } //while ($row = mysqli_fetch_array($results))
      } //if (mysqli_num_rows($results))
  } //if ($results = mysqli_query($cxn, $query))
?>

Hi all,

I have a number of tables within a mysql database and I'm stuck on how to pull information from one based on the results being displayed by another.

The site shows items for sale where by all the product information is held in tablea, when an individual item is being displayed I need to be able to pull a manufacturer profile from tableb based on the manufacturer name stored in tablea column profile. The manufacturer name is already being called into the page by existing code as the site has always shown the manufacturer name, I just can't work out out to pull the new profile information in as well from the new table.

Any help appreciated

Iain

What I'm trying to do here is generate a table based on a form in which the user selects two options. The first option tells the script which database entries to put in the table, the second option tells it how to arrange them. The first works perfectly, the second not at all - it doesn't produce an error, it just doesn't do anything. I've found a number of tutorials that seem to suggest that the problem is somewhere in my punctuation around ORDER BY '$POST[sort]' but I've been unable to find a solution that actually works. Any help would be much appreciated, my code is below.

Thank you!



mysql_select_db($database, $con);

$result = mysql_query("SELECT * FROM main WHERE state='$_POST[state]' ORDER BY '$POST[sort]'");
if(mysql_num_rows($result)==0){
 echo "<p align='left'>View Pantries by State</p><div id='stateform'>
<form action='../admin/viewstate.php' method='post'> <select name='state' />
<option value='AL'>Alabama</option>
<option value='AK'>Alaska</option>
<option value='AZ'>Arizona</option>
</select>
<select name='sort' />
<option value='name'>Name</option>
<option value='id'>Id</option>
<option value='city'>City</option>
<option value='zip'>Zip</option>
<option value='timestamp'>Timestamp</option>
</select>
<input type='submit' value='Go'></input></form>
</div>";
}
else{
echo "<p align='left'>View Pantries by State</p> <div id='stateform'>
<form action='../admin/viewstate.php' method='post'> <select name='state' />
<option value='AL'>Alabama</option>
<option value='AK'>Alaska</option>
<option value='AZ'>Arizona</option>
</select>
<select name='sort' />
<option value='name'>Name</option>
<option value='id'>Id</option>
<option value='city'>City</option>
<option value='zip'>Zip</option>
<option value='timestamp'>Timestamp</option>
</select>
<input type='submit' value='Go'></input></form>
</div>";
echo "<div id='fptext'><span class='h1'>Food Pantries for " . $_POST['state'] . "</span><br><br></div>"; }
echo "<table border='1' align='center' cellpadding='3' width='900px'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Type</th>
<th>Address</th>
<th>State</th>
<th>Phone</th>
<th>E-mail</th>
<th>Website</th>
<th>Hours</th>
<th>Requirements</th>
<th>Additional Information</th>
<th>Lat</th>
<th>Lng</th>
<th>Update</th>
</tr>";

while($row = mysql_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['name'] . "</td>";
  echo "<td>" . $row['type'] . "</td>";
  echo "<td>" . $row['address'] . "</td>";
  echo "<td>" . $row['state'] . "</td>";
  echo "<td>" . $row['phone'] . "</td>";
  echo "<td>";
  echo "<a href=mailto:".$row['email'].">".$row['email']."</a>";
  echo "</td>";
  echo "<td>";
  echo "<a href=".$row['website']."\>".$row['website']."</a>";
  echo "</td>";
  echo "<td>" . $row['hours'] . "</td>";
  echo "<td>" . $row['requirements'] . "</td>";
  echo "<td>" . $row['additional'] . "</td>";
  echo "<td>" . $row['lat'] . "</td>";
  echo "<td>" . $row['lng'] . "</td>";
  echo "<td><a href='../public/updatepage.php?id=".$row['id']."'>Update Pantry</a></td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);
?>

Hi all,
firstly apologies as this is a cross post from another forum and we have hit a block.. I am hoping that opening this up to another set of gurus we can get a resolution.

What I am trying to achieve is this...
I have 2 tables Main and FinancialYear. Main holds all data which I use a form to post the data to it..(all works fine). I use this code to create a drop down in the insert.php form. again this works.
Code: [Select]
<tr><td>Financial Year: xxxx/xxxx</td><td>
<!-- pulls the data from the table variable to populate the dropdown menu -->
   <?php
      $database = 'Projects_Main';
      $fintable = 'FinancialYear';
         if (!mysql_connect($db_host, $db_user, $db_pwd))
               die("Can't connect to database 'cos somethin' is wrong");
         if (!mysql_select_db($database))
             die("Can't select database");
      $result = mysql_query("SELECT FinancialYear_id, FinancialYear FROM {$fintable} order by FinancialYear");
      $options="";
         while ($row=mysql_fetch_array($result)) 
            {
               //$id=$row["FinancialYear_id"];
               $thing=$row["FinancialYear"];
               $options.="<OPTION VALUE=\"$thing\">".$thing.'</option>';
            }
   ?>
      <SELECT NAME="FinancialYear">
      <OPTION VALUE=0>Choose</OPTION>
      <?=$options?>
      </SELECT>
   </td></tr>

What I have done is built another form which list all records in the database and creates an update url for every record that passes the field Project_id where i use $_get to retrieve the Project_id to retrieve the relevant data into the update.php form.

I am able to populate the form with all the correct information BUT I am looking to introduce some dropdowns to aid updating the data and provide consistency to the data. .

Code: [Select]
// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db('Projects_Main')or die("cannot select DB");

// get value of id that sent from address bar
$Project_id=$_GET['Project_id'];
//define vars
$FinancialYear=$_POST['FinancialYear'];
// other vars defined here also.. about 30

// Retrieve data from database
$sql="SELECT * FROM Main WHERE Project_id='$Project_id'";
$result=mysql_query($sql);

$rows=mysql_fetch_array($result);
?>
//update_record_ac.php posts the data to the dbase.
<form name="form1" method="post" action="update_record_ac.php">
<center>
<table>
<tr><td><b>Store Details<b></td></tr>
<tr><td>Financial Year:</td><td>
// takes the data from $rows and present to form
<input name="FinancialYear" type="text" id="FinancialYear" value="<?php echo $rows['FinancialYear']; ?>">

// this is where I need to create the drop down.. see my other comments in the post.....

</td></tr>


the financialYear table consists if the following;
financialyear_id - pri, auto inc. ---- data format is 2010/2011, 2011/2012....
financialyear

the main table contains 30 fields .. won't list em all...
Project_id - pri, auto inc
financialyear

I need the drop down to pull the data from the financialyear table and then to present or focus on the currently stored data...
so if the store value in the table Main is 2010/2011 if Ii was to select the update url in the list_record.php it will pull all the relevant data into update_record.php form. the financialyear field in the form should be a dropdown with all the financial years listed but the 2010/2011 is selected or focused. I still need to be able to change the entry and post this back to the table Main.....

So the dropdown contains the list of years from the financialyear table but when the record is pulled from table main the year that is stored in table Main should be highlighted in the dropdown and I should be able to select a new record and post back to the table Main..


any thoughts...

please don't slate for the cross post, I haven't sanatised the data at any stage. I know i'm open to injection attacks. and yes my code is a little dirty... all these will be rectified as i finalise the process and ensure the consept works.

Thanks for taking the time to read and hopefully you are able to understand the requirement and are able to assist.

thanks
Balgrath

This topic has been moved to MySQL Help.

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