SQL Query and Display Problem

am 31.12.2006 10:04:07 von PHPBABY3

Hi,

1. I have two SQL tables. I will call them employees and departments:

EMP: LAST_NAME, FIRST_NAME, DEPTNM
DEPT: NUM, NAME

Input: text string FIND

Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
FIRST_NAME contains the string FIND in a case-insensitive manner.
Example: Employees with LAST_NAME = "Sandleburg" are listed when
input FIND = "BURG".

2. What if I have to add another field to the display, department
budget, from table:

ACCOUNTS: DEPTNM, BUDGET

3. Display the results 10 at a time.

4. Position the results on the page using CSS.

Thanks,

Peter

Re: SQL Query and Display Problem

am 31.12.2006 10:33:45 von Shion

PHPBABY3 wrote:
> Hi,
>
> 1. I have two SQL tables. I will call them employees and departments:
>
> EMP: LAST_NAME, FIRST_NAME, DEPTNM
> DEPT: NUM, NAME
>
> Input: text string FIND
>
> Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
> in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
> FIRST_NAME contains the string FIND in a case-insensitive manner.
> Example: Employees with LAST_NAME = "Sandleburg" are listed when
> input FIND = "BURG".

$query="SELECT * FROM EMP WHERE (LAST_NAME LIKE '%{$FIND}%') OR (FIRST_NAME
LIKE '%{$FIND}%')";

To convert the DEPTNM to NAME from the DEPT table, see
http://dev.mysql.com/doc/refman/5.0/en/join.html

> 2. What if I have to add another field to the display, department
> budget, from table:
>
> ACCOUNTS: DEPTNM, BUDGET

See: http://dev.mysql.com/doc/refman/5.0/en/join.html

> 3. Display the results 10 at a time.

Add to the query, where $startpoint= the rownumber you want to start at, minus
one (row 1 = 0, row 10 = 9, row 11 = 10).

$query.=" LIMIT {$startpoint},10";

> 4. Position the results on the page using CSS.

Go and ask a HTML newsgroup.

--

//Aho

Re: SQL Query and Display Problem

am 31.12.2006 10:33:45 von Shion

Re: SQL Query and Display Problem

am 31.12.2006 12:12:06 von ric

PHPBABY3 schrieb:
> Hi,
>
> 1. I have two SQL tables. I will call them employees and departments:
>
> EMP: LAST_NAME, FIRST_NAME, DEPTNM
> DEPT: NUM, NAME
>
> Input: text string FIND
>
> Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
> in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
> FIRST_NAME contains the string FIND in a case-insensitive manner.
> Example: Employees with LAST_NAME = "Sandleburg" are listed when
> input FIND = "BURG".

$sql = "SELECT A.LAST_NAME A.FIRST_NAME B.NAME FROM EMP A left join DEPT
B on A.DEPTNM = B.NUM WHERE A.LAST_NAME LIKE '%$FIND%';";

>
> 2. What if I have to add another field to the display, department
> budget, from table:
>
> ACCOUNTS: DEPTNM, BUDGET

add another left join as shown above

>
> 3. Display the results 10 at a time.

Add links to your page that contain the some sort of count, like:

and use LIMIT to only display from 11 to 20 if someone cicks next etc.:
http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.ht ml

>
> 4. Position the results on the page using CSS.

just add a class="cssname" to your div etc.

Whatever you want to display here a table , option list etc.

css:

#myoutput {
RIGHT: 10%; LEFT: 10%; WIDTH: 80%; POSITION: absolute; TOP: 100px;
}

>
> Thanks,
>
> Peter
>

Re: SQL Query and Display Problem

am 31.12.2006 12:12:06 von ric

Whatever you want to display here a table , option list etc.

css:

#myoutput {
RIGHT: 10%; LEFT: 10%; WIDTH: 80%; POSITION: absolute; TOP: 100px;
}

>
> Thanks,
>
> Peter
>

Re: SQL Query and Display Problem

am 01.01.2007 08:22:57 von Tim Roberts

"PHPBABY3" wrote:
>
>1. I have two SQL tables. I will call them employees and departments:
>
>EMP: LAST_NAME, FIRST_NAME, DEPTNM
>DEPT: NUM, NAME
>
>Input: text string FIND
>
>Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
>in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
>FIRST_NAME contains the string FIND in a case-insensitive manner.
>Example: Employees with LAST_NAME = "Sandleburg" are listed when
>input FIND = "BURG".
>
>2. What if I have to add another field to the display, department
>budget, from table:
>
>ACCOUNTS: DEPTNM, BUDGET
>
>3. Display the results 10 at a time.
>
>4. Position the results on the page using CSS.

It is *NOT* the job of the folks on this newsgroup to do your homework for
you, and those who replied should be ashamed at being suckered into doing
so.
--
Tim Roberts, timr@probo.com
Providenza & Boekelheide, Inc.

Re: SQL Query and Display Problem

am 01.01.2007 08:22:57 von Tim Roberts

Re: SQL Query and Display Problem

am 01.01.2007 21:52:23 von ric

Tim Roberts schrieb:
> "PHPBABY3" wrote:
>> 1. I have two SQL tables. I will call them employees and departments:
>>
>> EMP: LAST_NAME, FIRST_NAME, DEPTNM
>> DEPT: NUM, NAME
>>
>> Input: text string FIND
>>
>> Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
>> in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
>> FIRST_NAME contains the string FIND in a case-insensitive manner.
>> Example: Employees with LAST_NAME = "Sandleburg" are listed when
>> input FIND = "BURG".
>>
>> 2. What if I have to add another field to the display, department
>> budget, from table:
>>
>> ACCOUNTS: DEPTNM, BUDGET
>>
>> 3. Display the results 10 at a time.
>>
>> 4. Position the results on the page using CSS.
>
> It is *NOT* the job of the folks on this newsgroup to do your homework for
> you, and those who replied should be ashamed at being suckered into doing
> so.

Instead of posting useless comments to this ng you should invest your
time in a redesign of your corporate (probo) pages, next time try to
'not' copy a popular design.
Also mind your meta section "keywords", did you know that it's
forbidden to use trademarks from other companies as keywords in your
meta section?

By the way for statics, you don't need php :-)

Re: SQL Query and Display Problem

am 01.01.2007 21:52:23 von ric

Re: SQL Query and Display Problem

am 02.01.2007 04:09:23 von PHPBABY3

Tim Roberts wrote:
> "PHPBABY3" wrote:
> >
> >1. I have two SQL tables. I will call them employees and departments:
> >
> >EMP: LAST_NAME, FIRST_NAME, DEPTNM
> >DEPT: NUM, NAME
> >
> >Input: text string FIND
> >
> >Output: the LAST_NAME, FIRST_NAME and department name NAME (link DEPTNM
> >in EMP to NUM in DEPT) of all employees whose LAST_NAME or whose
> >FIRST_NAME contains the string FIND in a case-insensitive manner.
> >Example: Employees with LAST_NAME = "Sandleburg" are listed when
> >input FIND = "BURG".
> >
> >2. What if I have to add another field to the display, department
> >budget, from table:
> >
> >ACCOUNTS: DEPTNM, BUDGET
> >
> >3. Display the results 10 at a time.
> >
> >4. Position the results on the page using CSS.
>
> It is *NOT* the job of the folks on this newsgroup to do your homework for
> you, and those who replied should be ashamed at being suckered into doing
> so.

J.O.Aho and Ric and all the other nice people: please don't be bothered
by someone who is mean and nasty. We can just look in the other
direction.

Now, I've worked on my problem and have broken it down into 4
subroutines, that are used like this:

1. Create a button that will appear on all pages in the application at
a particular location using CSS, and that does as follows.

2. Read 2 fields CHOICE and FIND.
a. The top one, CHOICE, is a drop-down list with the same 16 choices
each time.
Function ddlits() returns the list of 16 literal values that are
displayed.
b. The bottom one FIND is a text field with no default, so it is
blank initially.

3. By special rules used only here, we translate (2) into a list of
(Query#,SQL Query) pairs.
Function qrz($choice,$find) = list of ($qnm,$sqry) pairs of Query# and
Query.

4. Execute (3) and accumulate the results into []=(Query#,Tuple). That
is, we have a function runsql($sqry) = list of tuples returned by query
$sqry.

5. Translate (Query#,Tuple) => (Display,URL) by function
qtdis($qnm,$tuple)=($display,$url) to create a list of links to
display.

6. Display the (Display,URL) pairs as links.

Can someone tell me how to implement this in CakePHP? Please be
explicit: I have to write what and store it where and do what to get
the button to appear on the existing pages?

Thanks,

Peter

> Tim Roberts, timr@probo.com
> Providenza & Boekelheide, Inc.

Re: SQL Query and Display Problem

am 02.01.2007 04:09:23 von PHPBABY3