db query

[Garry Smith]

I'm new to web2py, but do have a php and mysql background using codeigniter.

I'm trying to write a couple of queries but sure how to do them.

These are the queries I'm trying to write,

query1

SELECT user.s_name, user.f_name, user.year, pe.date, pe.sport, pe.id

FROM user

INNER JOIN pe

ON user.user_id=pe.user_id

WHERE pe.date = DATE(NOW())

ORDER BY user.year"

query 2

SELECT user.upn, user.f_name, user.s_name where user.upn = '4444'

 I tried this on the second query,

thelist=[row.first_name for row in db().select(db.pupils.first_name, db.pupils.upn')]

which pulls all records, need to know where to put  " where upn = '4444'"

Thanks in advance.

[Jim S]

thelist=[row.first_name for row in db(db.user.upn == 444).select()]

Have you read chapter 6 from the book?  It is well worth your time and should help with most of your questions.  http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer

-Jim

[Garry Smith]

Hi Thanks for the reply,

I have got query 2 working with the following:-

qry = request.vars.upn_no

q = (db.pupils.upn_no == qry)

s = db(q) 

row = s.select(db.pupils.upn_no,db.pupils.surname)

which gives me the following.

pupils.upn_no pupils.surname

5555                Dalglish

Once in the view is their a way off changing column headings and just showing what you want from the db.

[Jim S]

What does your view look like?

Also, this chapter should be helpful:  http://web2py.com/books/default/chapter/29/05/the-views

-Jim

[Garry Smith]

Hi

My view is basic at the moment just showing the column headings and results from db.

{{extend 'layout.html'}}

<h1>Results</h1>

{{=row}}

The headings show :-   pupil.upn_no and pupils.surname     with the results underneath.

what I would like to do is put the results in my own table.

coming from php I would do

<? foreach ($results as $peak) : ?>

<div (<?=$peak->peak_id?>)"><?=$peak->peak_area?></div>

<? endforeach; ?>

I have tried:_-

{extend 'layout.html'}}

<h1>Results</h1>

{{for r in row:}}

<p>r.pupils.upn_no<p>

{{pass}}

,but get no data back.

thanks again.

[Garry Smith]

Hi again after reading chapter 6,

I have found what I'm looking for.

I did the following and got thre results I wanted.

{{for r in row:}}

<p>{{=r.surname}}<p>

{{pass}}

One other question I would like to ask.

web2py works a lot like codeigniter for php using MVC, does web2py let you use jquery to send  data using  json ajax back to the controllers.

For example using:    <div id='tracks_selected'onclick='getTracks($album->track_id)  in the view.

Then use the following jquery

function getTracks(id) {

$.post("/media/getTracks", { id:id },

    function(data){

$('#tracks').html(data);

    });

}-

Which would go the a controller /media/tracks with the id number to be processes in the controller?

[Ron Chatterjee]

you need to do 

{{response.files.append(URL('static','js/java_script_file_I_am_runnning.js))}}

in web2py before ajax call layout.html

and then use 

<script>

your function

</script>

Correct me if I am wrong. Someone?

[Dave S]

On Monday, March 9, 2015 at 9:53:42 AM UTC-7, Ron Chatterjee wrote:

you need to do 

{{response.files.append(URL('static','js/java_script_file_I_am_runnning.js))}}

in web2py before ajax call layout.html

and then use 

<script>

your function

</script>

Correct me if I am wrong. Someone?

That step is either unnecessary or already done for you, because I didn't have to do it.

<DIV id="my_hats">
{{=LOAD(c='t_hats', f='t_hats2.load', target='my_hats', ajax=True)}}
    <span id="jsoffs">Javascript off?  <a href="t_hats2.html" id="jsoffa">Click here</a></span>
</DIV>

(If javascript is enabled, I set the span's visibility to 'hidden')

JQuery is covered in Chapter 11
<URL:http://www.web2py.com/books/default/chapter/29/11/jquery-and-ajax>
and LOAD() is covered in Chapter 12
<URL:http://www.web2py.com/books/default/chapter/29/12/components-and-plugins#LOAD>


It's easy enough to use, even I figured it out  :-)

Here's an example where I arm a button:

 dis_str = CAT(dis_str,
                TT("        "),
                INPUT(_name="derby", _type="hidden", value=index),
                TAG.BUTTON('derby promotion t=%s' % index,
                           _type="button", _name="proms%d" % (index),
                           _value=addy,
                           _onclick='ajax("promotes.load", ["addy","hname", "derby"], "my_promotions")',
                           _style="visibility:collapse", _id="promst%d" % (index)),
                BR())

and eventually dis_str gets sent to the client to display.

/dps

[Garry Smith]

Hi

Thanks for the reply

I have messing about a little with jquery today.

With some success, then not with others.

I put this code in a js file.

function DoAction( id )

{

    $.ajax({

         type: "POST",

         url: "http://127.0.0.1:8080/School_Clubs/default/test1",

         data: "id=" + id,

         

    });

}

 and this code in my view.

{{for r in row:}}

<a href="#" OnClick="DoAction('{{=r.upn_no}}');" > Click </a>

{{}pass}

This sends a number to a test1 controller, which works.

I use number in the controller, to get info from a db. eg. get a row id based on the number.

Then insert some data in a db after some checks.(got some parts working)

After that I want to redirect to a another page, is that possible.

Bear in mind that when I send the number via jquery to the controller it does it in the background,

it doesn't change page.

What I'm trying todo is as follows.

I have a search page, which searches the database for a person based on a number.

This then brings back the users name onto a results page.

I would then click on the user name which then registers the person in the database into

another table with the room and period.

This is what it does in the background.

It checks to see if that person is already in the a room with that period, if false it puts them in the room.

then goes back to the search page.

If true it goes back to the search page without adding the person.

This stops you registering the same person in the same period.

Ive done this in codeigniter php framework with no problems.

 

I want do it in web2py, this seems a lot less code for what I have archieved so for, plus

its a good way to learn web2py and find the good and things and limitations if any.

Thnaks