PHP - User Selects Checkbox Wich Columns To Retrieve

I am not sure if the title is correct; I tried my best. I'm a PHP/MySQL beginner and I really need some help. I have a small script that I am using for sending SMS. I recently added a phonebook. The problem with the phonebook right now is that it's available to all users, i.e. they can all update and delete all rows. What I would like to do is make it so that each user can update and delete only their own contacts. I have a table call contacts. Inside that table there is first name, last name, company and phonenumber. How can I accomplish this with PHP & MySQL?

CREATE TABLE IF NOT EXISTS `contacts` (
`contact_id` int(10) NOT NULL AUTO_INCREMENT,
`firstname` varchar(255) NOT NULL,
`lastname` varchar(255) NOT NULL,
`company` varchar(255) NOT NULL,
`cell_no` text NOT NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

CREATE TABLE IF NOT EXISTS `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`users_name` varchar(30) NOT NULL,
`uname` varchar(30) NOT NULL,
`u_pass` varchar(60) NOT NULL,
`utype` varchar(30) NOT NULL,
`timezone` varchar(30) NOT NULL,
`uapi_user` varchar(30) NOT NULL,
`uapi_pass` varchar(60) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=20 ;

<?php
if (isset($_POST['submit'])){
    //form has been submitted1
    $firstname  = trim($_POST['firstname']);
    $lastname   = trim($_POST['lastname']);
    $company    = trim($_POST['company']);
    $cellno = trim($_POST['cell_no']);

    if($firstname == ''){
        echo '<div class="alert alert-danger">First Name is not Valid!</div>';
        exit;
    }elseif($lastname == ''){   
        echo '<div class="alert alert-danger">Last Name is not Valid!</div>';
        exit;
    }elseif($company == ''){    
        echo '<div class="alert alert-danger">Company is not Valid!</div>';
        exit;
    }elseif($cellno == ''){ 
        echo '<div class="alert alert-danger">Cellphone Number is not Valid!</div>';
        exit;

    }else{

        $query = "Select cell_no from contacts where cell_no = '".$cellno."' ";
        $result = mysql_query($query);
        if (!mysql_num_rows($result)) {

        $sql = "INSERT INTO contacts(firstname, lastname, company, cell_no)
                            values('{$firstname}','{$lastname}', '{$company}', '{$cellno}')";
        $result = mysql_query($sql);
        confirm_query($result);

        //echo '<div class="alert alert-success">Successfully added.</div>';
        //exit;
        ?>
                    <script type="text/javascript">
            window.location = "contact_list.php";
        </script>
    <?php
    }
    else{

        echo '<div class="alert alert-danger">Username. already exist!.</div>';
        echo '<p><a href="new_contact.php" class="btn btn-success"> Back </a></p>';
        exit;

    }}

}else{
    $firstname  = "";
    $lastname   = "";
    $company    = "";
    $cellno = "";
}

?>

Am a newbie in php. Since I can't insert values to the database with respect to a user Id or with any other token using WHERE clause.

I.e "INSERT INTO receipts(date) VALUES(example) where id="**....."

If I need to fetch several values of column for a particular user, how do I go about it? Thank you!!!

Hey all,

THe problem I am having is even when I check the checbox, it still sends a value of 0 to the php array and hence nothing is ever updated:

Code: [Select]
<input type="checkbox" name="approved[1]" value="1" checked="checked"  />
<input type="checkbox" name="approved[3]" value="0"  />
<input type="checkbox" name="approved[4]" value="0"  />

So the second item is not checked by default. But even when I check it, my post array contains this:

Code: [Select]
array(2) { [1]=> string(1) "1" [3]=> string(1) "0" }

Notice the "0". It should be "1" now since I checked the second option.

This is the function:

Code: [Select]
function update(){
            $vanity_url = new VanityUrl();
            $checkbox = $this->input->post('approved'); //this is same thing as $_POST
            if(isset($checkbox)){
                foreach($checkbox as $key => $value){
                    $vanity_url->where('id',$key)->get();
            $vanity_url->approved = (int)$value;
                    $vanity_url->save();
                    $vanity_url->check_last_query();
                }
            }
        }

That check_last_query() function outputs this:

Code: [Select]
UPDATE `vanity_urls` SET `approved` = 1 WHERE `id` = 1

UPDATE `vanity_urls` SET `approved` = 0 WHERE `id` = 3

So despite checking the second option it still sets it to 0.

Thanks for response.

<script type="text/javascript">

Hey guys, I can't wrap my head around how to make this work right...

I have three tables:
Code: [Select]
CREATE TABLE `games` (
  `g_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`g_id`));

CREATE TABLE IF NOT EXISTS `game_player` (
  `r_id` int(11) NOT NULL AUTO_INCREMENT,
  `p_id` int(11) DEFAULT NULL,
  `g_id` int(11) DEFAULT NULL,
  `bool` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`r_id`));

CREATE TABLE IF NOT EXISTS `players` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `playerid` varchar(150) NOT NULL,
  PRIMARY KEY (`p_id`),
  UNIQUE KEY `playerid` (`playerid`));

The players table is my list of users, and they're tied to the list of games via the game_player table.

So here's my issue... I'm trying to show the full list of games, and then check mark each record where the player does play it. 
This is what I have so far - it shows all the games,  but it's not checking the boxes.

Code: [Select]
$result = mysql_query("SELECT * FROM games") or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$newquery = "SELECT * FROM game_player, players
WHERE game_player.p_id = players.p_id
AND game_player.g_id = ".$row['g_id']. "
AND players.playerid = {$userid}";
$query = mysql_query($newquery) or die(mysql_error());
if($query['bool'] == 1) { $set_checked = " CHECKED";
}
else{ $set_checked = "";
}
echo "<input type=\"checkbox\" name=\"box1\" value=\"".$query['g_id']."\"" . $set_checked . "/>".$row['name']."<br />\n";
}

Hello all,

   I am trying to learn OOP in PHP so please forgive my ignorance. I seem to be having difficulty inserting data into my database from checkbox items. My database has a column for each each fruit. I would like for each column to be populated with either a one(1) for checked or a zero(0)  for not checked. Currently, my attempts have failed. I can either get all checkboxes to insert all zeros or I can get the first column to insert a one(1) while the others as zeros. Is there anybody here that can help me figure out what I am doing wrong and help me to re-work my code so I can get this to work? If anybody can help me using OOP methods that would be fantastic. Thank you all in advance.

 

$preffruit->create_preffruit(array(

  'fruit_apple' => escape(Input::get(['fruit_selection'][0]) ? 1 : 0),
  'fruit_orange' => escape(Input::get(['fruit_selection'][1]) ? 1 : 0),
  'fruit_banana' => escape(Input::get(['fruit_selection'][2]) ? 1 : 0),
  'fruit_pear' => escape(Input::get(['fruit_selection'][3]) ? 1 : 0),
  'fruit_kiwi' => escape(Input::get(['fruit_selection'][4]) ? 1 : 0)

  ));

<input type="checkbox" name="fruit_selection[]" value="fruit_apple">
<input type="checkbox" name="fruit_selection[]" value="fruit_orange">
<input type="checkbox" name="fruit_selection[]" value="fruit_banana">
<input type="checkbox" name="fruit_selection[]" value="fruit_pear">
<input type="checkbox" name="fruit_selection[]" value="fruit_kiwi">



public function insert_preffruit($table, $fields = array()) {
			
			$keys = array_keys($fields);
			$values = '';
			$x = 1;			
			
			foreach($fields as $field) {
				$values .= '?';
				if($x < count($fields)) {
					$values .= ', ';
				}
				$x++;
			}		
				
				$sql = "INSERT INTO preffruit (`user_id`, `" . implode('`, `', $keys) . "`) VALUES (LAST_INSERT_ID(), {$values})";		
			
			if(!$this->query($sql, $fields)->error()) {
				return true;
			}			
			
			return false;
		}

 

Edited September 23, 2020 by ke-jo

Hi,

I have setup a standard contact form that sends an email using php "mail" function in my new website.
Today, I received an email from a potential client through my website. However, unfortunately, I did not receive the user's email address because (as I discovered after wards), there was a syntax problem in the function, so all I received is their name and message.
I am certain that the user has entered their email address because the form has validation, it just didn't send me the address so I have no way to contact that client...
Is there a way to retrieve the lost email address from the server somehow?

Thanks in advance

Okay, I currently have a query that is like:

Code: [Select]
$sql = "SELECT SQL_CALC_FOUND_ROWS a.ItemID, a.spec, a.description, a.sport, a.category, a.price, a.unit_qty, a.is_visible, a.AdminID,
b.name, b.ItemID as catID, c.Name as nameSport, c.ItemID as sportID
FROM purchase_request_category_item as a
LEFT JOIN purchase_request_category as b ON b.itemID = a.category
LEFT JOIN ro_school_sports as c ON c.ItemID = a.sport
WHERE a.AdminID = '".$dist."'
GROUP BY a.ItemID, b.name, c.Name
ORDER BY a.ItemID DESC LIMIT ".
$start.",".$limit;

I am building a custom search function which I intend to be able to change the WHERE line to:

WHERE a.AdminID = '".$dist."' ".$query


$query will be created via if statements for every element in the form which will be called either by the button on the form, or the links in my pagination.

The form would have the following:

Level(s): dynamic field, Multi Select
Status: Both/Enabled/Disabled single select
Sports: dynamic field, Multi Select

Category: dynamic field, single select.

the sport column is a single selected item on the add page, its of type int(11)

the levels table, is also a multi select on the add item page, and is of the type set('1','2','3','4','5','12','13')

So real question is I guess, how on earth do I work with these 2 multi selects to create this search? 

Hi Guys,

I am a typical newbie with a twist, i program in VB, recently moved to PHP.

I need help, preferable an example, but first let me explain what i have.

I have a .tpl page that has 3 drop down boxes, one of these is populated from within the .tpl page itself. The other 2 i waant to pupulate from a SELECT on a database from a .php file.

The .TPL file i have so far is:-

Code: [Select]
<style type="text/css">
<!--
.style7 {
   color: #006600;
   font-weight: bold;
   font-size: 18px;
}
.style14 {color: #003300; font-weight: bold; }
.style16 {
   color: #000066;
   font-style: italic;
}
.style25 {
   color: #FF0000;
   font-weight: bold;
   font-size: 12px;
}
.style28 {
   font-size: 12px;
   color: #330000;
}
.style29 {
   font-size: 12px
}
.style31 {color: #003300; font-weight: bold; font-size: 16px; }
.style33 {
   color: #000099;
   font-weight: bold;
   font-style: italic;
   font-size: 18px;
}
.style34 {
   color: #FF0000;
   font-weight: bold;
   font-size: 24px;
}
.style35 {font-size: 12px; color: #003300; }
.style36 {
   color: #FF0000;
   font-weight: bold;
   font-size: 14px;
   font-style: italic;
}
.style38 {color: #003300; font-weight: bold; font-size: 16px; font-style: italic; }
-->
</style>

  <script src="Scripts/AC_RunActiveContent.js" type="text/javascript"></script>
<h1 align="center" class="style34">Scotbirds Alertz - Rare and Scarce</h1>
<h3 align="center" class="style36">Soon Only VIP Members will be able to Access this Page</h3>
<h3 align="center" class="style14">If you have seen something unusual / rare then please call it in on -- <span class="style33">Hotline: 0333 5772473</span></h3>
<p align="center" class="style14">

<table width="90%" border="0" align="center" cellpadding="5">
  <tr>

  <?php print $_SERVER['PHP_SELF'];
     $the_date_filter = $_GET["DATE_FILTER"];?>

    <td width="1%"><td width="5%"><td width="2%"><td width="2%"><td width="2%"><form action="alertz_VIP.php" method="post">
    <td width="2%"><td width="2%"><td width="5%"><span class="style38">Date</span><td width="3%"></td>
    <td width="12%"><span class="style14">
      <select name=DATE_FILTER size="1" id=DATE_FILTER onchange="this.form.submit()">
        <option value="All Dates">All Dates</option>
        <option value="Today">Today</option>
        <option value="Last 48hrs">Last 48hrs</option>
        <option value="Last week">Last Week</option>
        <option value="last month">Last Month</option>
      </select>
    </span></td>
    <td width="1%">&nbsp;</td>
    <td width="7%"><span class="style16"><span class="style14"><span class="style31">Region</span></span></span></td>
   
        <td width="22%"><span class="style16"><span class="style14">
          <select name=REGION_FILTER size="1" id=REGION_FILTER onchange="this.form.submit()">
            <option value="AllRg">All Regions</option>
            <option value={REGION_FILTER}></option>
          </select>
        </span></span></td>
    <td width="1%">&nbsp;</td>

    <td width="8%"><span class="style16"><span class="style14"><span class="style31">Species</span></span></span></td>
    <td width="20%"><span class="style16"><span class="style14">
      <select name=SPECIES_FILTER size="1" id=SPECIES_FILTER onchange="this.form.submit()">
        <option value="AllSpec">All Species</option>
        <option value={SPECIES_FILTER}></option>
      </select>
    </span></span></td>
    <td width="9%"><input type="Submit" name="submit2" value="Search"  method="get" action="alertz_VIP.php" /></td>
</table>
<p>&nbsp;</p>
<table width="870" border="1" align="center" cellpadding="0" cellspacing="0" bordercolor="#99CC99">

  <tr>
    <th width="178" class="style31" scope="col">Region</th>
    <th width="184" class="style31" scope="col">Species</th>
    <th width="96" class="style31" scope="col">Date</th>
    <th width="96" class="style31" scope="col">Time</th>
    <th width = "304" class="style31" scope="col">Comments</th>
  </tr>
  <!-- BEGIN alerts -->
  <tr>
    <th class="style7 style29" scope="col">{alerts.REGION}</th>
    <th class="style25" scope="col">{alerts.SPECIES}</th>
    <th class="style35" scope="col">{alerts.DATE} </th>
    <th scope="col"><span class="style35">{alerts.TIME} </span></th>
    <th scope="col"><span class="style28">{alerts.COMMENTS} </span></th>
  </tr>
  <!-- END alerts -->
</table>
<p align="center" class="style14">&nbsp;</p>

Now from here i expect the user to select a date from the date box, ( Ideally i would like the other 2 options to only become visible once a selection on the date has been made, i would also like to retain the selected in the date box once the form has been submited.

Now the code i have for the PHP so far is quite long winded as my PHP skills are not so good, although i am learning fast.

Code: [Select]
<?php
/***************************************************************************
*                                Alertz.php
*                            -------------------
*   begin                : 30/10/04/10
*   copyright         : (C) 2010 Andy Guppy
*   email                : webmaster@scotbird.co.uk
*
*
***************************************************************************/

define('IN_ICYPHOENIX', true);

if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './');
if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1));

// Include files
include(IP_ROOT_PATH . 'common.' . PHP_EXT);
include_once(IP_ROOT_PATH . 'includes/functions_groups.' . PHP_EXT);
include(IP_ROOT_PATH . '/alerts/alertsconfig.'.PHP_EXT);

// Page Authorise
$cms_page_id = 'scotalertz';
$cms_page_nav = (!empty($cms_config_layouts[$cms_page_id]['page_nav']) ? true : false);
$cms_global_blocks = (!empty($cms_config_layouts[$cms_page_id]['global_blocks']) ? true : false);
$cms_auth_level = (isset($cms_config_layouts[$cms_page_id]['view']) ? $cms_config_layouts[$cms_page_id]['view'] : AUTH_ALL);
check_page_auth($cms_page_id, $cms_auth_level);

// Obtain Select Criteria
$temp_region_filter = $_REQUEST["REGION_FILTER"];
$temp_species_filter = $_REQUEST["SPECIES_FILTER"];
$temp_date_filter = $_REQUEST["DATE_FILTER"];

// Filter characters if required
$region_filter =str_replace(" & ", "&", $temp_region_filter);
$species_filter = $temp_species_filter;
$date_filter = $temp_date_filter;

// standard session management
$userdata = session_pagestart($user_ip);
// Check to see if user is logged in 
if ((!$userdata['session_logged_in']) )
   // No he isnt
   {
        redirect(append_sid(LOGIN_MG . '?redirect=alerts.' . PHP_EXT));
   
   }

else
   // Yes they are
   {
         
      init_userprefs($userdata);
      // set page title
      $page_title = "ScotBird alerts - VIP's ONLY !! ";

      // standard page header
      include(IP_ROOT_PATH . 'includes/page_header.'.PHP_EXT);
      
      // Connect to the database
      
      $db = new sql_db($alerts_mysql_host,$alerts_mysql_username,$alerts_mysql_password,$alerts_mysql_db,false);

       if(!$db)
      {
              die("Database Connection Failed:- Please Contact Site Admin" . mysql_error());
       }
      
      $template->set_filenames(array('body' => 'alertz_VIP.tpl'));

      if (!isset($_REQUEST['DATE_FILTER']))
      
      {
      
         // if date is NOT set then perform this
            echo 'Date has not been set';
            $sql2 = "SELECT * FROM alerts  ORDER BY Date DESC, time DESC  ";
            $sql3 = "SELECT DISTINCT species FROM alerts GROUP BY species";
            $sql = "SELECT DISTINCT region FROM alerts GROUP BY region";
            
            echo $sql2;
      }
      else
      {
         // if date is set then perform this
            $year =date("Y");
            $month = date("m");
            $day = date("d");
            $theenddate  = $year . '-' .$month . '-' . $day ;
            echo 'the choice selected was :- '.$_REQUEST['DATE_FILTER'];
         
         switch ($date_filter)
         {
            case "All Dates":
               $sql2 = "SELECT * FROM alerts  ORDER BY Date DESC, time DESC  ";
            break;
            case "Today":
               $sql2 = "SELECT * FROM alerts WHERE Date = '".$theenddate  ."'   ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date = '".$theenddate ."'  GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date ='".$theenddate ."' GROUP BY region";
            break;
            case "Last 48hrs":
               $year =date("Y");
               $month = date("m");
               $day = date("d")-1;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;   
            case "Last week":
               $year =date("Y");
               $month = date("m");
               $day = date("d")-7;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            case "last month":
               $year =date("Y");
               $month = date("m")-1;
               $day = date("d");
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            case "last 3 months":
               $year =date("Y");
               $month = date("m")-3;
               $day = date("d");
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  GROUP BY species";            
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            break;
            default:
               // Default is the last 48hrs
               $year =date("Y");
               $month = date("m");
               $day = date("d")-1;
               $thestartdate  = $year . '-' .$month . '-' . $day ;
               $sql2 = "SELECT * FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."'  ORDER BY Date DESC, time DESC  ";
               $sql3 = "SELECT DISTINCT species FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY species";
               $sql = "SELECT DISTINCT region FROM alerts WHERE Date BETWEEN '".$thestartdate ."' AND '".$theenddate ."' GROUP BY region";
            
         }
      }

      $result2 = $db->sql_query($sql2);
      if (!($result2 = $db->sql_query($sql2)))
      {
         die("Database Query Failed" . mysql_error());
      }
      $i  = 1;

      while ( $row2 = $db->sql_fetchrow($result2) )
         {
            $template->assign_block_vars('alerts', array( 'POS' => $i ,
                  'REGION' => str_replace("&", " & ", $row2['region']),
                  'SPECIES' => str_replace("%", "'",$row2['species']),
                  'DATE' => $row2['Date'],
                  'TIME' =>  $row2['time'],
                  'COMMENTS' => str_replace("'", "%",$row2['comments']),
                  )
               );
            $i++;
         }
      
      $template->assign_vars(array(
            'USERNAME' => htmlspecialchars($userdata[username]),
            'REGION_FILTER' => str_replace("&", " & ", $region_filter_options),
            'SPECIES_FILTER' => $species_filter_options,
            )
         );

      
      
   
      $result = $db->sql_query($sql);
    
      if (!($result = $db->sql_query($sql)))
         {
               die("Database Query Failed" . mysql_error());
         }

      // This is where you would add a new VARS Array if you intend to use your own custom VARS.
      
      
      while ($row = $db->sql_fetchrow($result))
         {
               $region_filter_options .= '<option value="' . $row['region'] . '">' . $row['region'] . '</option>';

         }
         
            
      
   
      $result = $db->sql_query($sql3);
    
      if (!($result = $db->sql_query($sql3)))
         {
               die("Database Query Failed" . mysql_error());
         }

      // This is where you would add a new VARS Array if you intend to use your own custom VARS.
      
      
      while ($row = $db->sql_fetchrow($result))
         {
               
            $species_filter_options .= '<option value="' . $row['species'] . '">' . $row['species'] . '</option>';
         }
         
         
      $template->assign_vars(array(
            'USERNAME' => htmlspecialchars($userdata[username]),
            'REGION_FILTER' => str_replace("&", " & ", $region_filter_options),
            'SPECIES_FILTER' => $species_filter_options,
            
         )
      );   
         
         
      // Build the page
      $template->pparse('body');

      // standard page footer
      include(IP_ROOT_PATH . 'includes/page_tail.'.PHP_EXT);

   }
?>
Now my questions are these:-

1) In the .tpl file hows can i submit from any of the dropdown boxes and retain the selection after submission
2) How can i have it so that the second and third drop down boxes are only visible after the previous one, ie the first box ( date ) has to have a selection before the Region ( 2nd one ) is visible and so
3) How can i reduce the amount of code to cover all options in building a select statement or is Switch - case the best way.

I would greatly appreciate help with this and even more so for some example of what i am asking for so i can learn from them.

Hello,   Any ideas why server resources not exhausted on the code below, when my.cnf and php.ini are both set to use half the resources each? PHP & MySQL will both utilize all allotted resources on other scripts, so it's not a tuning problem. There is no script-side tuning.   The bottleneck: pdo select as shown below

foreach($bigList as $listObject) {
                $sql = $dbl->prepare("SELECT * FROM fewMillionRows WHERE indexedCol1=:indexedCol1 AND indexedCol2=:indexedCol2 AND indexedCol3=:indexedCol3 AND indexedCol4=:indexedCol4 LIMIT 1");
                $sql->execute($preparedValues);
                $return = $sql->fetchAll(PDO::FETCH_ASSOC);
}

On a dedicated server with 8GB RAM, the server uses >5% cpu/ram but takes a long time to finish the script.     Second Question What are some alternative designs?   Because the column values all happen to be alphanumeric, I could select the entire table and store it in an array. Accessing the keys like so: indexedCol1indexedCol2indexedCol3indexedCol4.   Results: MyISAM - Select Whole Table: 30 seconds MyISAM – Select Individual Rows (10k times) – 68 seconds InnoDB – Select Whole Table: 30 seconds InnoDB – Select Individual Rows (10k times) – 131 seconds     I am surprised it takes so long to select a whole table. The server resources use 1% for about 20 seconds, then cpu/ram jump to 30%+ for about 10 seconds. This is still drastically faster than individual selects.   In this instance, $bigList is over 500k items. At 68 seconds per 10k rows it’s absurdly long. Building an array with key/values is the only realistic way I currently know of, but I suspect there is a much better way of doing this.     As far as I know, I cannot do a select like so: SELECT * FROM t1 WHERE (column1,column2,column3) IN  ((val1, val2,val3), (val4, val5,val6)) There is no way to determine whether a row was found for each entry as far as I know. 

Hi I would like to know how to make a link select certain stuff from the database. For example the link called cape town with the id 11 is selected then the link must search through the events table for the city_id 11 and take that information and display it in another page.

I would like to know how to do that or can you let me know of place that I can look. I have tried google but I don't know how to put what I'm looking for in words

Hi,   I would like to store sql query strings in a database. A query might look this like:   SELECT * from ListTable where id = '$id'   The idea would be to retrieve the string and run it as a SQL string. $id would be defined in the procedure so the idea is that when I send the SQL String through, $id would be replaced with its value and everything would work.   Under normal circumstances, the code would look like this:

$id = "somevalue";
$lcSQL = "SELECT * from ListTable where id = '$id'";

I would pass the SQL select off to mySQL and all is right with the world. I just want to store the string in a database, retreieve it and use it.   Thanks for your help in advance...    

Hey guys there is probably a simple fix to this but i am just not seeing it    ... Im createing a form that has error catching and self submitting valuse. I am trying to use a for loop to create all of the values and if the form is returned with an error then the correct values will still be filled out but i cant seem to get this loop to work. Every time i submit the form it doesnt matter what option i select it always comes back with the last one in the list or 30 in this case.

the problem code is below.. Any help would be appreciated.

Code: [Select]

<?php
for($i=1; $i<=30; $i++){
echo'<option value="'.$i.'"';
if(isset($_POST['petsAge'])== $i){ echo ' selected="selected"';}
echo '>'.$i.'</option>';
}
?>

Hi guys, thank for any help. This does not appear like a difficult problem but it has me stymied. I have several selects, the 1st item of each is "--Specify--" so that the user has to specifically choose an item. Once an item is selected on any select the others should be set to the 1st item or "--Specify--" to avoid confusion.

If you want to see the complete code it on this web page: http://chordsandguitar.com/progressions-test.php

The relevant code is:

Code: [Select]
if ($prog1_menu != "--Specify1--")
{
$prog2_menu = "--Specify--";
echo "set prog2 to Specify";
}

if ($prog2_menu != "--Specify2--")
{
$prog1_menu = "--Specify--";
echo "set prog1 to Specify";
}

echo "<form action = \"progressions-test.php?s_sort_all=$sort_all&s_prog1_menu=$prog1_menu&s_prog2_menu=$prog2_menu&s_prog1_trigged=$prog1_trigged&s_prog2_trigged=$prog2_trigged\" method = \"GET\">";
//also stores the values of the drop-downs when Select button is pushed so they can be retrieved when the web page is loaded again



echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ascending bass line";
echo "<br />";
echo "Select a progression: <a href=\"http://www.chordsandguitar.com/progressions-test.php?s_sort_all=All&s_prog1_menu=--Select--&s_prog2_menu=--Select--\"\"><big>ALL</big></a>";
echo "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;";

echo "<select name=\"prog1_menu\"  onchange=\"this.form.submit();\">";

echo "<option value=\"--Specify1--\">--Specify--</option>";
if ($prog1_menu == "ii-iii-IV-V progression") echo "<option SELECTED value=\"ii-iii-IV-V progression\">ii-iii-IV-V progression</option>";
else echo "<option value=\"ii-iii-IV-V progression\">ii-iii-IV-V progression</option>";
if ($prog1_menu == "I-ii-IV-V progression") echo "<option SELECTED value=\"I-ii-IV-V progression\">I-ii-IV-V progression</option>";
else echo "<option value=\"I-ii-IV-V progression\">I-ii-IV-V progression</option>";
if ($prog1_menu == "I-iii-IV-V progression") echo "<option SELECTED value=\"I-iii-IV-V progression\">I-iii-IV-V progression</option>";
else echo "<option value=\"I-iii-IV-V progression\">I-iii-IV-V progression</option>";
echo  "</select>";

echo "&nbsp;&nbsp;&nbsp;";

echo "<select name=\"prog2_menu\" onchange=\"this.form.submit();\">";

echo "<option value=\"--Specify2--\">--Specify--</option>";
if ($prog2_menu == "I-sharpIo7-ii7-sharpIIo7 progression") echo "<option SELECTED value=\"I-sharpIo7-ii7-sharpIIo7 progression\">I-#Io7-ii7-#IIo7 progression</option>";
else echo "<option value=\"I-sharpIo7-ii7-sharpIIo7 progression\">I-#Io7-ii7-#IIo7 progression</option>";
if ($prog2_menu == "I-sharpIo7-ii7-V7 progression") echo "<option SELECTED value=\"I-sharpIo7-ii7-V7 progression\">I-#Io7-ii7-V7 progression</option>";
else echo "<option value=\"I-sharpIo7-ii7-V7 progression\">I-#Io7-ii7-V7 progression</option>";
echo  "</select>";

echo "</form>";
I am trying to identify which select box has been used so that I can set all the other boxes to the 1st item but I wind up re-setting all the boxes.

Cheers,

Jacques

Hi i have this drop down list for date which contain 3 selects DAY MONTH YEAR hwo can i make so that when update form select keeps the same value has before help please

<?php
$months = array('','January','February','March','April','May','June','July','August','September','October','November','December');
echo '<select name="month_of_birth">';
for ($i=1;$i<13;++$i) {
   echo '<option value="' . sprintf("%02d",$i) . '">' . $months[$i] . '</option>';
}
echo '</select>';
echo '<select name="day_of_birth">';
for ($i=1;$i<32;++$i) {
   echo '<option value="' . sprintf("%02d",$i) . '">' . $i . '</option>';
}
echo '</select>';
echo '<select name="year_of_birth">';
$year = date("Y");
for ($i = $year;$i > $year-50;$i--) {
   $s = ($i == $year)?' selected':'';
   echo '<option value="' . $i . '" ' . $s . '>' . $i . '</option>';
}
echo '</select>';
?>