Page 1 of 1

on click sql query on same page for calendar

Posted: Sun May 17, 2009 11:16 am
by tsetsuit
Ok having a bit of an issue with a calendar I am building. This is my first build using PHP so I am very new to it please let me know if I am messing anything up in process.

for the quick and simple, I am trying to have a link post a sql query in same page. In the calendar all you see if the type of request such as "meeting" , "maintenance" , or "other" the date you schedule it.
I have the input page to datebase working fine using a <form action='insert.php' method='post'> (code) </form>

Now the problem I am having is if i click on meeting, maintenance or other i want it to run a statement like

Code: Select all

 
("select * from postings where id = (id of the posting)");
 
now what I do not know how to do is get the id of the posting by clicking on that link to run query against it.

I have read through $_get statements to get the id but I am really still trying to figure that out completely for getting table ids. Plus I want to link this on same page so i don't think doing a

Code: Select all

 
$id = $_get('id') <a href='page.php?=$id>page</a> 
 
will work because that posts to a new page.

I have really been fighting this for last 2-3 days and tried to figure it out on my own before posting and had no luck.
posting some of the usefull code and datebase structure up below if anyone has any advice would be much appreciated. Thank you


SQL database info
changed some of the numbers and letters in database to prevent conflicts such as month=m0nth or date=da17
  • id (auto_increment)
  • m0nth
  • da17
  • year
  • user_id
  • type
  • subject
  • p0sting
  • emails

This is what i use to populate the calendar with the scheduled dates, I know I still need to add mysql_real_escape_string into my select statements for security, still working on reading through how that works will input before I complete this.

Code: Select all

 
    $txtmon = "'" . date("F", $date) . "'";
    $now = getdate();
 

Code: Select all

 
 
    if( $now['mday'] == $day && $now['month'] == date("F", $date) )
        {
            echo "<td class='test' align='top' valign='left'><a href='test2.html'>" . $day . "</a>";
            echo "<table width=30px height=60px><tr><td class='inner' align='top' valign='left'>";
                $posting = mysql_query("SELECT type,rows FROM postings WHERE da13=$day AND m0nth=$txtmon"); 
                while($row = mysql_fetch_array($posting))
                      {
                      echo "<br />";
                      echo "<a href=''>" . $row['type'] . $row['rows'] . "</a>";
                      echo "<br />";
                      }
            echo "</td></tr></table>";
            echo "</td>";
        } else {
            
            echo "<td class='dat' align='top' valign='left'><a href='test2.php'>" . $day .  "</a>";
            echo "<table width=30px height=60px><tr><td class='inner' align='top' valign='left'>";
                $posting2 = mysql_query("SELECT type,rows FROM postings WHERE da13=$day AND m0nth=$txtmon");
                while($row = mysql_fetch_array($posting2))
                      {
                      echo "<br />";
                      echo "<a href=''>" . $row['type'] . $row['rows'] . "</a>";
                      echo "<br />";
                      }
            echo "</td></tr></table>";
            echo "</td>";
            
        };
    }
 
 
I want to post the SQL query in a jquery accordion to the right of the calendar. So when you click on the scheduled meeting or maintenance it pulls the information open to the right for the subject and who is getting e-mailed for it.

I have a cron job doing the e-mailing which is working fine.
Please any information would be much appreciated thank you

Re: on click sql query on same page for calendar

Posted: Sun May 17, 2009 3:47 pm
by califdon
What you're looking for is Ajax, which is using a Javascript object to make an asynchronous request back to a server to get data which can then be used to modify the page already being displayed in the browser, without a page reload. Ajax is not another language, it's a technique that uses Javascript in the browser in concert with PHP (or other server script) on the server.

Check out http://www.w3schools.com/Ajax/Default.Asp and other online tutorials.

Re: on click sql query on same page for calendar

Posted: Sun May 17, 2009 4:06 pm
by tsetsuit
Thank you so much!!!!! I have been fighting this for last 3 days and this will atleast get me in right direction. :)

Re: on click sql query on same page for calendar

Posted: Sun May 17, 2009 8:30 pm
by tsetsuit
Ok ajax got me closer to where I want to be but I end up right back at the same questions. when i click on the link to run the query how do I run a query against a field.
so say you click on This link and the way that link was put there is off of a statement like

Code: Select all

 
$url = "whatever"
$sql= mysql_query("SELECT link FROM table where name=$url");
 
while($row = mysql_fetch_array($sql))
{
    echo "so say you click on" . "<a href onclick=switch($id)>" . $row['link'] . "</a>" . "and the way";
}
 
ok so say that code is real and that when i click on the link i want to run this query

Code: Select all

 
$sql = mysql_query("Select * from table where id="whatever the id is of that row");
 
<ajax to insert it on same page>
 
 
 
I am still stuck where I don't know how to get the id of the row to displace it all on the page.
Ajax showed me how to display it on same page that helped me get to next step after i figure this one out.

Re: on click sql query on same page for calendar

Posted: Sun May 17, 2009 9:07 pm
by califdon
If I'm understanding you correctly, this is the same issue whenever you want to search for a particular record in a database. Either you know the value for the primary key, in which case you use that and get, at most, one record, or you search on another field, in which case there's a possibility that more than record will be found to match the criterion. So if you're doing the latter, you have to write your code so that it handles 3 cases quite differently:
1) No matching records are found; so you advise the user that no matching record was found.
2) Precisely one matching record was found; so that's it, you display it.
3) More than one matching record was found; so you must display a list of (usually) several fields from each record, and on the same line, a link or button to allow the user to select which one of the records is the one they are interested in. This isn't hard to do in PHP, it would look something like this:

Code: Select all

$sql="SELECT * FROM tblWhatever WHERE lname='$searchname'";
$res=mysql_query($sql) or die(mysql_error());
$numrows=mysql_num_rows($res);
switch $numrows {
   case 0 :
      // ... echo "No record found": or something
      break;
   case 1 :
      // ... fetch the one record and display it
      break;
   case default :
      while($row=mysql_fetch_assoc($res)) {
         extract($row);
         echo "<tr><td>$field1</td><td>$field2</td><td><a href='xyz.php?id='" . $idfield . "'>Select Here</a></td></tr>";
      }
}
 
In the event they search for "Smith" and 17 results are found, they can see all of them, with first name and maybe address, and just by clicking on "Select Here" on the appropriate line, another script can be called with a $_GET parameter that is the correct value of the primary key, so you can retrieve the specific record desired.

Hope that helps.

Re: on click sql query on same page for calendar

Posted: Sun May 17, 2009 9:24 pm
by tsetsuit
ok but how do i pass the query by just clicking on the link. I understand making a function to click on the link but how do i find that one record.
for example if you look at the calendar you see

"meeting" for may 29th (this is row 13)
"meeting" for may 27th (this is row 14)
"maintenance" for may 29th (this is row 15)
"other" for may 15th (this is row 16)
"maintenance" for may 20th (this is row 17)

you will see this in grid form though like a calendar on your wall.
now when i click on say the word meeting that is in the box for the 29th how do i pass a query to get the information from row 13. I know (select * from table where row=$id) but how do i get $id to hold the value 13 to make it link to that query.
I can use ajax to display it on the page but i just need to know how to get the data. And thank you ahead of time for being patient with me in this and giving multiple explinations.

Re: on click sql query on same page for calendar

Posted: Mon May 18, 2009 11:10 am
by califdon
That's what I showed you in line 14 of my previous post. When you create the page that shows the 17 (let's say) Smiths, in a while loop, you build the link (the <a href='xxxxxx.php?id=' . $id . "'>") so that each link already has the proper primary key set up as part of the URL. Thus, when you write your xxxxxx.php script, you use $_GET['id'] value in your query.