By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,782 Members | 1,154 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,782 IT Pros & Developers. It's quick & easy.

SQL Query and Display Problem

P: n/a
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

Dec 31 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Dec 31 '06 #2

P: n/a
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:

<a href="yourpage.php?count=11>Next 10</a>

and use LIMIT to only display from 11 to 20 if someone cicks next etc.:
http://dev.mysql.com/doc/refman/5.0/...imization.html
>
4. Position the results on the page using CSS.
just add a class="cssname" to your div etc.

<div class="myoutput">
Whatever you want to display here a table , option list etc.
</div>

css:

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

Peter
Dec 31 '06 #3

P: n/a
"PHPBABY3" <sh********@gmail.comwrote:
>
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, ti**@probo.com
Providenza & Boekelheide, Inc.
Jan 1 '07 #4

P: n/a
Ric
Tim Roberts schrieb:
"PHPBABY3" <sh********@gmail.comwrote:
>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 :-)
Jan 1 '07 #5

P: n/a
Tim Roberts wrote:
"PHPBABY3" <sh********@gmail.comwrote:

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, ti**@probo.com
Providenza & Boekelheide, Inc.
Jan 2 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.