Help with an Array function.

Joshua · October 11, 2010

I'm working on a large mod to say the least, and I for the life of me can't figure out how to do the following.

I am inserting employees using explode/implode into a database.

employees will all be on one database row 1,4,11 etc.

Now easily enough using implode/explode I can say..call the employees from the database.

My problem is, there is a certain part I want to just print the total of employees in that company

i.e 1, 4, 11 = 3

How would I do this using the array feature, would I need to use foreach?

while($br = $db->fetch_row($banks))
{

$boom = $br['bankemployees'];

$array = array($boom);

$count = implode(",", $array);

The above is just what i'm using to echo the employee's ID's seperated by a ,

a_bertrand · October 11, 2010

Why not let the DB make it for you with something like

[mysql]

SELECT COUNT(*) FROM MyTABLE WHERE COMPANY=X

[/mysql]

Joshua · October 11, 2010

I wasnt sure the database would seperate the ,'s

i,e database structure is

Bid bankname bankowner bankemployees bankranking

Edit Delete 1 joshy 1 1, 2, 3 1

I need the total of 1,2,3 i.e = 3

I'll give what you suggested a try and see if that won't work I may just be overthinking the scenario

Edit:

UERY ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 2, 3' at line 1

Query was SELECT COUNT(*) FROM `userbanks` WHERE `bankemployees`=1, 2, 3

Is what I kinda thought would have happened, stupid databases >,< ;P

Joshua · October 11, 2010

N/m I figured it out, thanks for the attempt


while($br = $db->fetch_row($banks))
	{

	$blam = $br['bankemployees'];

	$count = explode(",", $blam); // Destroy the , in the database for Printing out all employees later.

	$totals = array();


			foreach($count as $item)
			{
			isset($totals[$item]) ? $totals[$item] = 1 : $totals[$item]++; 
			}

	echo ''.array_sum($totals).''; // Testing Array Sum Total



echo '<tr><td>'.$br['bankname'].'</td>
   <td>'.$br['bankowner'].'</td>
   <td></td>
   <td>'.$br['bankranking'].'</td>
   <td>[url="bank.php"]Select Bank[/url]</td>
   </tr>';


	echo 'Welcome to the bank yahoo pages.  
	Here you can select a bank.'; // This will have a paginated index of all User Owned Banks.

	}	
}

a_bertrand · October 11, 2010

Well don't know your DB design... Anyhow having rows containing multiple values is not very smart (if this is the design you use currently) as you cannot do much without going though some code, where the DB could do all the work for you.

For example if you need to retrieve multiple ID => SELECT * FROM XYZ WHERE ID IN(1,2,3)

Joshua · October 11, 2010

Right, The thing I'm going for however is there won't be one defined ID that i can just set permanently.

There will be multiple users in multiple columns

I.E

One bank may have 10 employees with various ID's

One bank may have 100 employees etc. etc.

On the banking list i'm going to have it show the total employees for that bank which are all stored in 1 column of the banking for optimiziation purposes.

but it's also going to call to that array later on for the Employee purposes also.

If employee is in this bank, they have more rights as loan officers, collectors , etc.

So i'm not quite sure I could use the in() feature you're referring to?

a_bertrand · October 11, 2010

Let's create some tables:

[mysql]

CREATE TABLE EMPLOYEE(

ID INTEGER PRIMARY KEY AUTO_INCREMENT,

FIRST_NAME VARCHAR(80),

LAST_NAME VARCHAR(80));

CREATE TABLE BANK(

ID INTEGER PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(80));

CREATE TABLE EMPLOYEE_ROLE(

ID INTEGER PRIMARY KEY,

NAME VARCHAR(80));

INSERT INTO EMPLOYEE_ROLE(ID,NAME) VALUES

(1,'loan officers'),

(2,'collectors'),

(3,'ceo');

CREATE TABLE BANK_EMPLOYEES(

BANK_ID INTEGER NOT NULL,

EMPLOYEE_ID INTEGER NOT NULL,

ROLE_ID INTEGER NOT NULL,

PRIMARY KEY(BANK_ID,EMPLOYEE_ID));

[/mysql]

Now let's add some data to play with:

[mysql]

INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME) VALUES

('A','Toto'),

('B','Tata'),

('C','Titi'),

('D','Rolf');

INSERT INTO BANK(NAME) VALUES

('Bank A'),

('Bank B');

INSERT INTO BANK_EMPLOYEES(BANK_ID,EMPLOYEE_ID,ROLE_ID) VALUES

(1,1,1),

(1,2,2),

(2,3,1);

[/mysql]

Now that we have some data, we can start querying:

Retreive all employees of 'Bank A'

[mysql]

SELECT EMPLOYEE.* FROM EMPLOYEE,BANK_EMPLOYEES WHERE BANK_EMPLOYEES.EMPLOYEE_ID=EMPLOYEE.ID AND BANK_EMPLOYEES.BANK_ID IN (SELECT ID FROM BANK WHERE NAME='Bank A')

[/mysql]

Or let's count them:

[mysql]

SELECT COUNT(*) FROM EMPLOYEE,BANK_EMPLOYEES WHERE BANK_EMPLOYEES.EMPLOYEE_ID=EMPLOYEE.ID AND BANK_EMPLOYEES.BANK_ID IN (SELECT ID FROM BANK WHERE NAME='Bank A')

[/mysql]

Or let's count all the employees for all banks:

[mysql]

SELECT COUNT(*),BANK.NAME FROM BANK_EMPLOYEES,BANK WHERE BANK.ID=BANK_EMPLOYEES.BANK_ID GROUP BY BANK.ID

[/mysql]

Etc... if you need more queries let me know ;)

Joshua · October 11, 2010

I'm not 100% on that particular route, I may give it a shot, but i've been playing with code for the past 4 hours and finally got it to work the way I want.

Granted it's just a "start" but it's doing what I want it to do.

Ignore the SELECT *s in the queries as they will be better defined later on, it's just for ease of use right now

<?php

	require_once('globals.php');


switch($_GET['action']) 
{
case "bankindex":bankindex();break;
case 'openbank': openbank(); break;
case 'remove': remove(); break;
default:bankindex();break;
}




		function bankindex()
		{
		global $ir, $db, $userid, $h;

	$banks = $db->query("SELECT * FROM `userbanks` ORDER BY `Bid` ASC");

              if(!$db->num_rows($banks))
              {
              echo 'There are currently no banks available from which to choose';
              $h->endpage();
              exit;
              }

                             echo '[b]Welcome to the central banking station.[/b]

                             Here you can find a large selection of banks with various options

                             to fit your specific needs.  Please feel free to browse around and 

                             be sure and select the bank that best suits you.



                             If you are seeking employment with a bank you can find the application section

                             On that banks homepage as well as whether or not they are currently hiring.



                             '; // This will have a paginated index of all User Owned Banks.

		echo '<table width=70% class=table><tr><th>Banking Name</th>
           <th>Bank Owner</th>
           <th>Bank Employees</th>
           <th>Bank Ranking</th>
           <th>Select Bank</th></tr>';


			while($br = $db->fetch_row($banks))
			{

			$blam = $br['bankemployees'];
			// Define all bank employees to a simple variable

			$count = explode(",", $blam); 
			// Destroy the , in the database for Printing out all employees later.

			$totals = array();
			// Using Totals and Foreach will help grab the total amount of employees



				foreach($count as $item)
				{
				isset($totals[$item]) ? $totals[$item] = 1 : $totals[$item]++; 
				}
				//This is selecting the Total Count of Employees

	$tezz = $db->query("SELECT u.*, b.* 
						FROM users u 
						LEFT JOIN userbanks b 
						ON u.userid=b.bankemployees 
						WHERE u.userid IN($count[0])");

						$rar = $db->fetch_row($tezz);
						$check = $rar['userid'];
					// Now we select the bank where the Current User is in, if not we kill the query using 
					// if(!$db->num_rows($tezz)){die('you're not employeed with a bank');
					// $h->endpage(); exit; }

		echo '<tr><td>'.$br['bankname'].'</td>
		<td>'.$br['bankowner'].'</td>
		<td>'.array_sum($totals).'</td>
		<td>'.$br['bankranking'].'</td>
		<td>[url="userbanks.php?action='.$br['Bid'].'"]Select Bank[/url]</td>
		</tr>';




				}    

echo '</table>';

echo '[url="userbanks.php?action=openbank"]Start your own bank now![/url]';
			}




function openbank()
{
global $banks, $ir, $db, $h;

$alreadyhave = $db->query("SELECT * FROM userbanks WHERE bankowner=".$ir['userid']."");

if($db->num_rows($already))
{
echo 'It appears you already own a bank at this time.';
$h->endpage();
exit;
}



/*$find = $db->query("SELECT * FROM userbanks WHERE ".$ir['userid']." IN(`bankemployees`)");

$ff = $db->fetch_row($find);

$db->query("UPDATE userbanks SET bankranking = 500 WHERE ".$ir['userid']." IN(bankemployees)");
*/

}

function loanrequest()
{
global $banks;

echo 'This will be where users can request a loan from a loan officer that is employed with the bank.'; // Loan officers will be employees with the bank
}

function userbank()
{
echo 'This will be where users can deposit money into their bank account';//User Owned banking.
}

Only 1 SQL table made so far.

-- phpMyAdmin SQL Dump

-- version 3.2.4

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Oct 11, 2010 at 09:43 AM

-- Server version: 5.0.91

-- PHP Version: 5.2.9

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Table structure for table `userbanks`

--

CREATE TABLE IF NOT EXISTS `userbanks` (

`Bid` int(11) NOT NULL auto_increment,

`bankname` varchar(255) NOT NULL,

`bankowner` int(11) NOT NULL,

`bankemployees` varchar(255) NOT NULL,

`bankranking` int(11) NOT NULL,

PRIMARY KEY (`Bid`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--

-- Dumping data for table `userbanks`

--

INSERT INTO `userbanks` (`Bid`, `bankname`, `bankowner`, `bankemployees`, `bankranking`) VALUES

(1, 'joshy', 1, ' 1,2,3', 500),

(2, 'this and that', 2, '3,2,5,335', 333);

a_bertrand · October 11, 2010

Well I see already the problem here:

"`bankemployees` varchar(255) NOT NULL"

Means when your array list is over 255... you will not be able to store more people inside. What about large games with a huge number of people inside? Ok let's take ID of players are 5 char each (means max number is 99999 so under 100K registrations which is small) means you can have max 42 employees per bank (as you need to count 6 char (5+1) per player).

Also, try to make a query which returns the more powerful bank (by checking how much money each employees have) and you start to have loops... which is slow.

So overall I would go for my own design but of course you are free to do whatever you want ;)

Joshua · October 11, 2010

Hm, Didn't think enough into the length which should have been obvious.

Though of course the amount of employees could be limited.

Hm, Now i must take this into consideration.

a_bertrand · October 11, 2010

Well, that why you took the right road to ask before having your product finished ;)

Keep in mind that my design, even if more scary due to its more table design, is a lot more powerful and allows you to create reports of all sorts. And this is exactly where a DB is useful, not just to store data (otherwise you could as well "serialize" your arrays on files).

Now I understand some queries can be more difficult to write, but I'm willing to help (join the IRC chat and hope I'm there or drop a line here).

Joshua · October 11, 2010

I'm fairly well versed with the SQL and it's set-up although there are a few features I dont use often.

But I do appreciate the info and I'll keep that in mind, going to give your set-up a try as you did have a valid point ^_^

Joshua · October 12, 2010

Using a method similar to what you were referring to A_bert

<?php

		require_once('globals.php');



				switch($_GET['action']) 
				{
				case "bankindex":bankindex();break;
				case 'bankpage': bankpage(); break;
				case 'remove': remove(); break;
				default:bankindex();break;
				}


		function bankindex()
		{
		global $db, $ir, $h;


		// bid for BANK ID, bname for BANK NAME, bowner for BANK OWNER, 
		// bvault for BANK VAULT, binterest for BANK INTEREST RATE  --Bank Tables


			echo '[b]Welcome to The User Bank Index[/b]



			Users Are able to create their own banking system, define their interest

			Hire employees, and much much more.  Giving you the the chance to choose the right

			Bank for you!

			If you come here seeking employment please refer to the bank page for hiring options.





					<table class=table>
					<tr><th>Bank Name</th>
					<th>Bank Owner</th>
					<th>Bank Interest</th>
					<th>Bank Employees</th>
					<th>Bank Page</th>
					</tr>';

$bankq = $db->query("SELECT `b`.`bname`,`b`.`binterest`,`b`.`bid`,`b`.`bowner`, " .
						"`u`.`username`,`u`.`userid`  " .
						"FROM `bank` `b`  " .
						"LEFT JOIN `users` `u`  " .
						"ON `u`.`userid`=`b`.`bowner` " .
						"ORDER BY `bid` ASC");
				// $bankq is the mysql_query joining banks and users.

		while($rar = $db->fetch_row($bankq))
		{
		echo '<tr>
		<td>'.stripslashes(htmlentities($rar['bname'])).'</td>  // We must remember to secure input as well.
		<td>'.$rar['username'].'</td>
		<td>'.abs((float) $rar['binterest']).'</td>'; //abs float secures decimal although it should already be.


				$total = $db->query("SELECT `bank_employee` 
				FROM `bankemployees` 
				WHERE (`bank_id`=".abs((int) $rar['bid']).")");

				$amount = $db->num_rows($total);
						// This is Selecting the total # of employees for each bank

	echo '<td>'.$amount.'</td>
			<td>[url="userbanks.php?action=bankpage&ID='.abs((int) $rar['bid']).'"]Bank Home Page[/url]</td>
			</tr>';
		}
		}         
	function bankpage()
	{
	global $db, $ir, $h;
	}

?>

You're right, it's much simpler with plenty of additional features.

a_bertrand · October 12, 2010

Use the count(*) instead of selecting all and then checking how many rows it returns. It will be both faster for the database as well as for PHP and will be at the end lighter for your server:

Bad:

$total = $db->query("SELECT `bank_employee` 
                   FROM `bankemployees` 
                   WHERE (`bank_id`=".abs((int) $rar['bid']).")");

                   $amount = $db->num_rows($total);

Good:

$total= $db->query("SELECT count(`bank_employee`)
                   FROM `bankemployees` 
                   WHERE (`bank_id`=".abs((int) $rar['bid']).")");

Beside that I'm glad to be helpful ;)

Joshua · October 12, 2010

What am I missing with your query.

Apparently I'm not as versed as I thought because it won't echo the correct amount of employees ><,

Djkanna · October 12, 2010

Well what does it return?

------------------------------------

$total = $db->fetch_single($db->query("SELECT count(`bank_employee`)
                   FROM `bankemployees` 
                   WHERE (`bank_id`=".abs((int) $rar['bid']).")"));
echo $total;

// 2

Joshua · October 12, 2010

$total = $db->query("SELECT count(`bank_employee`) \"TOT\" FROM `bankemployees` WHERE (`bank_id`=".abs((int) $rar['bid']).")");

had to define the column i'm calling then call to it properly using fetch_row['column_name']

medz · October 18, 2010

Hi joshua,did you get my pm?

Thanks!

Sign In

Help with an Array function.

Recommended Posts

Joshua

a_bertrand

Joshua

Joshua

a_bertrand

Joshua

a_bertrand

Joshua

a_bertrand

Joshua

a_bertrand

Joshua

Joshua

a_bertrand

Joshua

Djkanna

Joshua

medz

Join the conversation

Browse

Activity

Hosting