Sum, Update Sort & Data Subset

[nassausky]

1)I Did Set $debug_mode to 1 in /include/config.php

2)DaDaBIK Version 4.2
Server:Server version: 5.0.27-standard-log
Protocol version: 10
Server: Localhost via UNIX socket
Client: Windows XP SP2, MySQL client version: 4.1.10
IE7

3)No errors Just questions below

A) How can I add a value like the sum of 2 fields. For example after a user enters field1=10 and field2=20 then it automatically place the sum 30 (field1+field2) for field3.

B) After I insert a new record it messes up the sort order.

C) How can I select a subset of data.. For example just the records where field1 start with the letter "A"

 

[DebbieS]

A ... Don't know about sums except that you could try and play with custom code or back-end functions -- maybe someone else can provide more insight into this one.

B ... After record insert, the newest record is displayed first. If this is not appropriate or correct for your installation, click on Show All to re-sort.

C ... Answered previously in the forums -- search on custom query or personalized query. I know there is a post that I contributed to with a drop-down solution.

 

[nassausky]

Thankssss again you did alot of answering for me today

Regarding:

A) Hmmm was thinking there was some simple method I was missing.. wouldn't know where to go in code to sum it up after the entry.. If I knew the embed point maybe I could make heads or tail of it...

B)After record insert, the newest record isn't coming up for me.. It displays the browse table in the reverse sort order of the primary key field ..lol

C)I found some solutions that might point me in the right direction after a bit of studying but none are so direct with a small clip of code.. I was figuring I can create an URL with a where_clause and a starts_with to give that subset.. The purpose is something probably real common that everyone has sent up once or twice.. Basically it's purpose is for a 1000 record or so database. Want to index it by first letter. User knows the title of the item and clicks on it and it jumps to just the corresponding query results.. Simple as making an url with the right contruct...

Thanks Debbie for all of your time.. Still looking and trying and I am enjoying this a bit Who said I can't have some fun working with it

Mike

 

[billthecat]

As far as A goes, why not just run an UPDATE on field 3?

<?php
$result = mysql_query("SELECT * FROM yourtable");

while($row = mysql_fetch_array($result))
{
$updaterow3 = $row['row1'] + $row['row2'];
mysql_query("UPDATE yourtable SET row3 = '$updaterow3'");

}
?>

 

[nassausky]

Thanks, it does make sense but not sure where to embed that code


Mike

 

[billthecat]

pretty much anywhere...you could even make it a seperate file "updatefield3.php", then include it right under the header include:
include ("./include/updatefield3.php");


just make sure the file is in the "include" directory

 

[nassausky]

Excellent thanks.. Will play with it tomorrow.. it's almost 3am here in NY.. wiped Getting some sleep but got alot figured out ...

Mike

 

[DebbieS]

nassausky ... a link to display all records that start with A, B, C, etc... was posted in this topic (the 6th post on the page in particular has the correct one. In my tests in my installs, this works to display all records for a particular field that start with the given letter. The link can be used to create an A-Z link row for the users.

For one of my installs, the users preferred to have a drop-down with all the letters, so I'm not using this link code actively. The drop-down method is described in this post (approx 7th post down).

 

[nassausky]

Thanksssssssssss Debbie, Bill and everyone else that helped.. Got alot figured out.. This answers my question (C) from above and someone elses question of how to make an alphabetical index for a database without using a drop down menu which can take a little longer to navigate...

So answering my own question with the help of many in this site.. Here is some code for anyone to use which I am using now and it is simple but real helpful.

First let me explain what it is..

It's a an index that looks something like this

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

In other words a horizontal table where each link is clickable and jumps you to that letter of the alphabet with your choice of field and can be embedded on the header or footer (or any page for that matter)

For example in one site i'm designing it will jump to the user choice of video title. For another site it's going to jump to the last name in a Who's Who page I'm creating... Here is the code:

#Note I am using videos1 as my database here
#Note I am using videos1.NAME field for my index search
#Note I am using the 'A%' thru 'Z%' in the for loop to fill in the navigation items with the CHR(65) thru CHR(90) ascii conversion function

echo "<center><table border=\"1\"><tr>";
for ($i=65;$i<=90;$i++){
$LetterLink="http://mysite.org/databikfolder/?function=search&table_name=videos1&where_clause=videos1.NAME+LIKE+%27".CHR($i)."%25%27";
$WebLink3="".CHR($i)."";
echo "<td>".$WebLink3."<//td>";
}
echo "<//tr><//table></center>";


#1 Line sets up the initial table to be drawn out.
#2 Line starts our for loop with the 26 letters of the alphabet (using CHR conversion from ASCII)
#3 Line I created a temporary variable $LetterLink to store the URL
#4 Line I created another temp variable $WebLink to store the needed code to make a URL Link in html
#5 Line I added the link inside the table data
#6 Line I closed out my table..

If this code helps any newbies or experts. Would love to know

Goodluck,
Mike

 

[nassausky]

To take it a step further, I am considering putting 2 or more indexes on top (or beside) where one index is a quick navigation to maybe last names another is a search by another column(s)