Jump to content

AdRock

Members
 View Profile  See their activity

  • Posts

    913

  • Joined

  • Last visited

 Content Type 

Posts posted by AdRock

    Select all rows from tables joining other table but select only rows that match a condition

    in MySQL Help

    Posted

    I have 2 queries that I want to join together to make one row

     
    This queries returns all rows from both tables which is what i want
    SELECT 
	table_A.*,
	table_B.*
FROM table_A
	INNER JOIN 
		table_B ON 
			table_A.code = table_B.code

    and this is the output

    table_A.id 	|   table_A.code  |   table_B.id  |   table_B.code 	 | 	 table_B.complete
===============================================================================================
1		|      123456     |       1    	  |     123456	 	 |   	yes
2	       	|      654321     |       2   	  |   	654321 		 | 	no

    and this is the second query

    SELECT 
	table_C.*,
	table_D.*
FROM table_C
	INNER JOIN 
		table_D ON 
			table_C.code = table_D.code
	INNER JOIN 
		table_B ON 
			table_D.code = table_B.code
WHERE table_B.complete = 'yes'

    and again the output

    table_C.id  |   table_C.code  |   table_D.id  |   table_D.field2   |    table_B.complete
========================================================================================
1    	    |    123456       |       1       |       123456	   |	     yes

    What I've been trying to for the last couple of days is join the 2 queries together to make one query that returns this

    table_A.id 	|   table_A.code  |   table_B.id  |   table_B.code 	 | 	 table_B.complete	|   table_C.id  |   table_C.code  |   table_D.id  |   table_D.field2   |    table_B.complete
====================================================================================================================================================================================
1			|	   123456     |       1    	  |     123456	 	 |   	yes				|       1    	|     123456      |   	  1       |       123456	   |		yes
2	       	|      654321     |       2   	  |   	654321 		 | 		no

    All i want it to do is get all rows from tables A and B and return all rows from C and D only if table_B.complete equals "yes".  All tables are joined by the code column which all have the same value

    split array by first part of key name into other arrays

    in PHP Coding Help

    Posted

    I have an array like this

     

    $rows = array(
	array(
		'fruit.name' => 'Apple',
		'fruit.colour' => 'Red',
		'fruit.weight' => '0.1',
		'vegetable.name' => 'Carrot',
		'vegetable.colour' => 'Orange',
		'vegetable.weight' => '0.05'
	),
	array(
		'fruit.name' => 'Banana',
		'fruit.colour' => 'Yellow',
		'fruit.weight' => '0.7',
		'vegetable.name' => 'Potato',
		'vegetable.colour' => 'Brown',
		'vegetable.weight' => '0.6'
	)
);

    And i want to be able to sort the array into 2 other arrays called 'fruits' and 'vegetables' based on the first part of the key name so up to the decimal point.  With this array I should have 2 rows in each of the fruits and vegetable arrays.

     

    I have this code but it doesn't work and I can't see what I'm doing wrong.

    $fruits = array();
$vegetables = array();

foreach($rows as $row)
{
	foreach($row as $key => $value) 
	{
		if('fruit' == substr($key, 0, strpos($key, '.')))
		{
			$fruits[$key] = $row;
		}
		else
		{
			$vegetables[$key] = $row;
		}
		
	}
}

echo "<pre>"; var_dump($fruits); echo "</pre>";

    When i do a var_dump i get this

     

    array(3) {
  ["fruit.name"]=>
  array(6) {
    ["fruit.name"]=>
    string(6) "Banana"
    ["fruit.colour"]=>
    string(6) "Yellow"
    ["fruit.weight"]=>
    string(3) "0.7"
    ["vegetable.name"]=>
    string(6) "Potato"
    ["vegetable.colour"]=>
    string(5) "Brown"
    ["vegetable.weight"]=>
    string(3) "0.6"
  }
  ["fruit.colour"]=>
  array(6) {
    ["fruit.name"]=>
    string(6) "Banana"
    ["fruit.colour"]=>
    string(6) "Yellow"
    ["fruit.weight"]=>
    string(3) "0.7"
    ["vegetable.name"]=>
    string(6) "Potato"
    ["vegetable.colour"]=>
    string(5) "Brown"
    ["vegetable.weight"]=>
    string(3) "0.6"
  }
  ["fruit.weight"]=>
  array(6) {
    ["fruit.name"]=>
    string(6) "Banana"
    ["fruit.colour"]=>
    string(6) "Yellow"
    ["fruit.weight"]=>
    string(3) "0.7"
    ["vegetable.name"]=>
    string(6) "Potato"
    ["vegetable.colour"]=>
    string(5) "Brown"
    ["vegetable.weight"]=>
    string(3) "0.6"
  }
}

    Any help please getting this to separate the array into 2 arrays each containing either fruits or vegetables.

     

    radio button value 0 in $_POST shows "on" in var_dump

    in PHP Coding Help

    Posted

    Yes. That was a typo and I've narrowed it down to a form class I use that generated form elements.

    Actually writing the html markup does work but my form class seems to drop the zero value.

    I might have to set the form values to yes/no and then convert that to a 0/1 before it goes into database.

     

    Anyway thanks for your help. At least it helped my identify the problem

    radio button value 0 in $_POST shows "on" in var_dump

    in PHP Coding Help

    Posted

    Whenever I try and post a form with radio buttons like this

    <input type="radio" name"whatever" value="0">

    and I do a

    var_dump($_POST)

    it always shows 

    array(11) {["whatever"]=> string(2) "on" }

    but this works and will display 1 in the var_dump

    <input type="radio" name"whatever" value="1">

    Why is this. I need the value set to 0 because that's the value going into the database. I don't really want to do a str_replace just to replace "on" with "0"

     

    Bullet proof sessions not working

    in PHP Coding Help

    Posted · Edited by AdRock

    I have been following an article on creating bullet proof sessions but I'm having problems with session variables i'm creating getting destroyed

     

    I call the session_start() like this

    SessionManager::sessionStart('MySession', 0, '/', 'localhost');

    But when i try to add new session vars, i think the preventHijacking() function is is getting called for some reason and it wipes out the session and creates a new one.

     

    Any ideas how I can get this to work?

     

    Here is the link http://blog.teamtreehouse.com/how-to-create-bulletproof-sessions

     

    And here is the complete code

    class SessionManager{
static function sessionStart($name, $limit = 0, $path = '/', $domain = null, $secure = null)
{
// Set the cookie name
session_name($name . '_Session');


// Set SSL level
$https = isset($secure) ? $secure : isset($_SERVER['HTTPS']);


// Set session cookie options
session_set_cookie_params($limit, $path, $domain, $https, true);
session_start();


// Make sure the session hasn't expired, and destroy it if it has
if(self::validateSession())
{
// Check to see if the session is new or a hijacking attempt
if(!self::preventHijacking())
{
// Reset session data and regenerate id
$_SESSION = array();
$_SESSION['IPaddress'] = $_SERVER['REMOTE_ADDR'];
$_SESSION['userAgent'] = $_SERVER['HTTP_USER_AGENT'];
self::regenerateSession();


// Give a 5% chance of the session id changing on any request
}
elseif(rand(1, 100) <= 5)
{
self::regenerateSession();
}
}
else
{
$_SESSION = array();
session_destroy();
session_start();
}
}


static protected function preventHijacking()
{
if(!isset($_SESSION['IPaddress']) || !isset($_SESSION['userAgent']))
return false;


if ($_SESSION['IPaddress'] != $_SERVER['REMOTE_ADDR'])
return false;


if( $_SESSION['userAgent'] != $_SERVER['HTTP_USER_AGENT'])
return false;


return true;
}


static function regenerateSession()
{
// If this session is obsolete it means there already is a new id
if(isset($_SESSION['OBSOLETE']))
return;


// Set current session to expire in 10 seconds
$_SESSION['OBSOLETE'] = true;
$_SESSION['EXPIRES'] = time() + 10;


// Create new session without destroying the old one
session_regenerate_id(false);


// Grab current session ID and close both sessions to allow other scripts to use them
$newSession = session_id();
session_write_close();


// Set session ID to the new one, and start it back up again
session_id($newSession);
session_start();


// Now we unset the obsolete and expiration values for the session we want to keep
unset($_SESSION['OBSOLETE']);
unset($_SESSION['EXPIRES']);
}


static protected function validateSession()
{
if( isset($_SESSION['OBSOLETE']) && !isset($_SESSION['EXPIRES']) )
return false;


if(isset($_SESSION['EXPIRES']) && $_SESSION['EXPIRES'] < time())
return false;


return true;
}
}

    A better way of looping using foreach and switch

    in PHP Coding Help

    Posted · Edited by AdRock

    Is there a nicer, cleaner more efficient and less code way of achieving this?

     

    I have an array of rows from the database and 2 of the columns could have either of 2 values which give me 4 possibilities.

     

    I want to loop through each of them and assign the row to one of 4 arrays so they are all separated out.

     

    This is what i have so far. Haven't tested it yet but the theory makes sense.

     

    if(!empty($rows)) {
	foreach($rows as $key => $row) {
		switch($row['type']) {
			case 'post':
				switch($row['archived']) {
					case 0:
						$array1[$key] = $row;
						break;
					case 1:
						$array2[$key] = $row;
						break;
					default:
				}
				break;
			
			case 'event':
				switch($row['archived']) {
					case 0:
						$array3[$key] = $row;
						break;
					case 1:
						$array4[$key] = $row;
						break;
					default:
				}
				break;
			default:
		}
		
	}
}

    Divs nesting in foreach loop when they shouldn't

    in PHP Coding Help

    Posted

    I have problem with my code.

     

    What i'm trying to do is loop through a 2d array first looking at the level so i should have 8 <div class="group">.  I then need to loop through the inner part of the array and i need to create groups of 4 so i should start with <div class="slide"> and close it after 4 items and if there are more repeat this process.

     

    The problem lies with this part.  If i take it out it works but i need to get this in like shown below in my example output

     

    Problem code

    <?php if ($count % NUMCOLS == 0) echo '<div class="slide">';  # new row ?>

<?php echo $count; ?>

<?php $count++; ?>

<?php if ($count % NUMCOLS == 0) echo '</div>';  # new row ?>

    Full code

    <?php define ("NUMCOLS",4); ?>
<?php foreach ($this->level as $level => $courses): ?>    
<?php $count = 0; ?>
<div class="group" id="level<?=$level;?>">
    <div class="controls"><button class=prev>Previous</button> <button class=next>Next</button></div>
    <div class="pics" data-fx="scrollLeft" data-speed=300>
    <?php foreach ($courses as $course => $records): ?>
    <?php foreach ($records as $record): ?>
    
    <?php if ($count % NUMCOLS == 0) echo '<div class="slide">';  # new row ?>
    
    <?php echo $count; ?>
    
    <?php $count++; ?>
    
    <?php if ($count % NUMCOLS == 0) echo '</div>';  # new row ?>

    <?php endforeach; ?>
    <?php endforeach; ?>
    </div>
</div>
<?php endforeach; ?>

    Actual output

    <div class="group" id="level1">
    <div class="controls"><button class=prev>Previous</button> <button class=next>Next</button></div>
    <div class="pics" data-fx="scrollLeft" data-speed=300>
        <div class="group" id="level2">
            <div class="controls"><button class=prev>Previous</button> <button class=next>Next</button></div>
            <div class="pics" data-fx="scrollLeft" data-speed=300>
                <div class="slide">
                
                </div>
                <div class="slide">
                
                </div>
            </div>
        </div>
    </div>
</div>

    desired output

    <div class="group" id="level1">
    <div class="controls"><button class=prev>Previous</button> <button class=next>Next</button></div>
    <div class="pics" data-fx="scrollLeft" data-speed=300>
        <div class="slide">
        
        </div>
        <div class="slide">
        
        </div>
    </div>
</div>

<div class="group" id="level2">
    <div class="controls"><button class=prev>Previous</button> <button class=next>Next</button></div>
    <div class="pics" data-fx="scrollLeft" data-speed=300>
        <div class="slide">
        
        </div>
        <div class="slide">
        
        </div>
    </div>
</div>

    Outputting multidimensional array

    in PHP Coding Help

    Posted

    I have an array like but larger and it could have different types and levels.  This array is taken from MySQL and I've called it $courses

     

    

    array(23) {
    

      [0]=>
    

      array(8) {
    

        ["name"]=>
    

        string(43) "Conservation of Decorative Arts"
    

        ["type"]=>
    

        string(12) "conservation"
    

        ["url"]=>
    

        NULL
    

        ["level"]=>
    

        string(1) "1"
    

      }
    

      [1]=>
    

      array(8) {
    

        ["name"]=>
    

        string(32) "Diploma in Restoration"
    

        ["type"]=>
    

        string(12) "conservation"
    

        ["url"]=>
    

        NULL
    

        ["level"]=>
    

        string(1) "1"
    

      }
    

      [2]=>
    

      array(8) {
    

        ["name"]=>
    

        string(20) "Diploma in Finishing"
    

        ["type"]=>
    

        string(12) "craft"
    

        ["url"]=>
    

        NULL
    

        ["level"]=>
    

        string(1) "1"
    

    }
    

    What I want to do is output the array so i can echo out the Name and URL and order it by Type so i get something like
    

    <h1>Conservation</h1>
<ul>
<li><a href=""></a></li>
<li><a href=""></a></li>
<li><a href=""></a></li>
</ul>
<h1>Craft</h1>
<ul>
<li><a href=""></a></li>
<li><a href=""></a></li>
<li><a href=""></a></li>
</ul>
    

    I 've tried using nested foreach() loops but it prints the same value out multiple times so instead of about 15 rows, i end up with about 100 with loads of duplicate rows one after the other
    

     
    

    // $this->courses is the MySQL array
<?php foreach($this->courses as $course): ?>
<?php foreach($course as $key => $value): ?>

<li><?=$course['name'];?></li>

<?php endforeach; ?>
<?php endforeach; ?>
    

    What am i doing wrong?
    

    

     
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    12. 

	
	
  12. 
		
	
    13. 

	
	
  13. 
		
    

			

			
    	
				
    
					
    
						
						
      
							
							
						
    
					
    
					



    
	
    
		
			
		
			Php not running if statement
		
	
    
	
    in PHP Coding Help
    

    
				
    
				
				
    
					
    
						
						
    
							Posted 
							
								· 


	Edited  by AdRock
	
	

							
														
						
    
						

						
    
							
							

    Instead of querying the database for all the users, why don't you do a SELECT COUNT from `users` WHERE username=$username
    

     
    

    Do a numrows and if it's 1 then it's taken else it's free.
    

     
    

    Just noticed in your while loop were you set $i = 1, you should exit the loop otherwise it will overwrite it
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    14. 

	
	
  14. 
		
    

			

			
    	
				
    
					
    
						
						
      
							
							
						
    
					
    
					



    
	
    
		
			
		
			PHP Notice:  Undefined offset: 1
		
	
    
	
    in PHP Coding Help
    

    
				
    
				
				
    
					
    
						
						
    
							Posted 
							
														
						
    
						

						
    
							
							

    I have this line of code where i want to get the value of SUM(amount)
    

    list($number,$sum) = $this->db->query('SELECT COUNT(*), SUM(amount) FROM dc_donations');
    

    but I get this notice
    

    PHP Notice:  Undefined offset: 1 in C:\www\mvc\models\donate_model.php on line 42
    

     
    

    At the moment the table is empty and the amount column is of type float so when i run the query through phpMyAdmin it comes back with NULL for the amount.
    

     
    

    I need that amount for this line so if the table is empty, it's obviously 0
    

    // Calculating how many percent of the goal were met:
$percent = round(min(100*($sum/GOAL),100));
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    15. 

	
	
  15. 
		
    

			

			
    	
				
    
					
    
						
						
      
							
							
						
    
					
    
					



    
	
    
		
			
		
			PDO: General error: 1366 Incorrect integer value
		
	
    
	
    in PHP Coding Help
    

    
				
    
				
				
    
					
    
						
						
    
							Posted 
							
														
						
    
						

						
    
							
							

    I have a database class that uses PDO and it all works until i need to do an update where I increment the value
    

     
    

    What I have is a table and one of the columns is called 'counter' and I need to update this table every time a page is visited
    

     
    

    This is the function that handles all the PDO and creates the query
    

     /**
     * update
     * @param string $table A name of table to insert into
     * @param string $data An associative array
     * @param string $where the WHERE query part
     */
    public function update($table, $data, $where)
    {
        ksort($data);
        
        $fieldDetails = NULL;
        foreach($data as $key=> $value) {
            $fieldDetails .= "`$key`=:$key,";
        }
        $fieldDetails = rtrim($fieldDetails, ',');
        
        $sth = $this->prepare("UPDATE $table SET $fieldDetails WHERE $where");
        
        foreach ($data as $key => $value) {
            $sth->bindValue(":$key", $value);
        }
        
        $sth->execute();
    }
    

    and this is how I call the function by passing parametres to the function
    

    $board = ucwords(str_replace('-',' ',$board));
$sql = "SELECT boardid from boards WHERE boardname = :board";
$rows = $this->db->select($sql, array(':board' => $board));
        
$postData = array(
      'counter' => 'counter+1',
);
        
$this->db->update('topics', $postData, "`topicid` = {$topic} AND `boardid` = {$rows[0]['boardid']}");
    

    It fails the update with this error message
    

     
    

     PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1366 Incorrect integer value: 'counter+1' for column 'counter' at row 1' in C:\www\mvc\libs\Database.php:143
    

    The counter column is of type INT
    

     
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    16. 

	
	
  16. 
		
	
    17. 

	
	
  17. 
		
	
    18. 

	
	
  18. 
		
	
    19. 

	
	
  19. 
		
	
    20. 

	
	
  20. 
		
	
    21. 

	
	
  21. 
		
    

			

			
    	
				
    
					
    
						
						
      
							
							
						
    
					
    
					



    
	
    
		
			
		
			Get position in array of certain value
		
	
    
	
    in PHP Coding Help
    

    
				
    
				
				
    
					
    
						
						
    
							Posted 
							
														
						
    
						

						
    
							
							

    I have this array and I need to search it for the 'id' for a specific value $id.  What i'm trying to do is get the position in the array of the id i'm searching for.
    

     
    

    I have tried 
    

    $position = array_search($id, $rows);
    

    but I get bool false
    

     
    

    The contents of $rows (taken from MySQL database) is
    

    array( {
[0]=>
array(1) {
["id"]=>
string(2) "24"
}
[1]=>
array(1) {
["id"]=>
string(2) "22"
}
[2]=>
array(1) {
["id"]=>
string(2) "12"
}
[3]=>
array(1) {
["id"]=>
string(2) "11"
}
[4]=>
array(1) {
["id"]=>
string(1) "8"
}
[5]=>
array(1) {
["id"]=>
string(1) "6"
}
[6]=>
array(1) {
["id"]=>
string(1) "5"
}
[7]=>
array(1) {
["id"]=>
string(1) "4"
}
}
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    22. 

	
	
  22. 
		
	
    23. 

	
	
  23. 
		
    

			

			
    	
				
    
					
    
						
						
      
							
							
						
    
					
    
					



    
	
    
		
			
		
			PDO MySQL fulltext searches
		
	
    
	
    in PHP Coding Help
    

    
				
    
				
				
    
					
    
						
						
    
							Posted 
							
														
						
    
						

						
    
							
							

    Hi mac_guver
    

     
    

    The query works when i'm not using placeholders as I've used it before when i first wrote it but I just want to updated it to be used with PDO.
    

     
    

    I haven't tested it yet but just wanted to know if it's in the right format for PDO.
    

    I read somewhere that you can only use a placeholder once but because i'm only binding the search values once, can the same placeholder be used in different parts of the query?
    

     
    

    I will test it and see what happens
    

						
    
					
    
					
						
    
							

	
    	
		
    
			
				
				
    
					
				
    
			
			
			
		
    
	
    

						
    
					
				
    		
			
    
		
    
	
    24. 

	
	
  24. 
		
	
    25. 

	
	
  25. 
		
	
    26. 

			

		

	
	

		


	
	

	





			

		


	




						


					

					


					

				

			

			
		

		
		


	

		×
		

			
				

					
				

			

			

			

			
		

	






	

		×
		

			
    
				
  • Create New...
    • 
				
			

		

	



		






















	

		

			
Important Information

			
We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.