Runing a personalizated Query

[rootuid]

Hi,
I'm looking for a url link to a specific query not a form.For example I want a url that links to "all locations that =x" in my database.I don't know how to go about this besides writing php code to query the database directly.

Any suggestions.

 

[alpha2zee]

The 'case : search' in 'switch function' in index.php leads all queries. It uses $_POST values (that is, form values submitted by the POST method) to generate a 'where_clause' that is used to query the database. This where_clause is passed as a $_GET value in the page number links if the results are spread over more than a page.
[pre]
http://stanxterm.aecom.yu.edu/secondary/ordering/interface_creator/index_long.php?&table_name=vendor&function=search&where_clause=%60vendor%60.%60Name%60+LIKE+%27%25a%25%27+and+%60vendor%60.%60Phone%60+LIKE+%27%258%25%27&page=1&order=Name&order_type=ASC
[/pre]
So you can have text linked to such a URL to lead people to results of predefined queries.



Post Edited (11-15-05 05:51)

 

[rootuid]

Thanks for the reply.I tried the above on my database:

http://ie.frequencydb.com/index.php?table_name=frequencies&function=search&where_clause=Cork&page=0&order=Location&order_type=ASC

but I get a :
[08] Error: during query execution.

The where_clause confuses me.I want to select all entries from field Location which equal Cork Give it a go on my database http://ie.frequencydb.com/ !

 

[alpha2zee]

The where_clause is first generated by DaDaBik from the $_POST variables using these parameters -

-table_name
-field_name (only one, LOCATION, in your case; can be many)
-field_value (CORK in your case; can be many; one for each field_name)
-separator (and, or; when more than one field_name)
- select_type (e.g., field value equals or contains 'abc')

In the example I give in my last post, you can discern these in the where_clause value.
[pre]
where_clause=%60vendor%60.%60Name%60+LIKE+%27%25a%25%27+and+%60vendor%60.%60Phone%60+LIKE+%27%258%25%27
[/pre]
vendor is the table_name; Name and Phone are field_names; a and 8 are field_values; and is separator; the two LIKE are the two select_types; the % characters are URL-encodings (such as %60 for ` [backquote])
The where_clause is used for the MySQL SELECT statement .
--

You should copy the link for page 2 (in your search results for Location = Cork), and then change page=1 to page=0 to get the link you want. Thus,
[pre]
http://ie.frequencydb.com/index.php?&table_name=frequencies&function=search&where_clause=%60frequencies%60.%60Location%60+%3D+%27Cork%27&page=0&order=Frequency&order_type=ASC&PHPSESSID=468cfd97a2c0f6f47089245889ad9dbf
[/pre]



Post Edited (11-16-05 00:40)

 

[rkholmes]

This question would be for Debbie or anyone with a better handle on PHP/MySQL programming then my own.

In the snippet copied below (which is part of Debbie's solution for canned queries to Dadabik

***************************************

// get the number of XXX records in the database
$sql = "SELECT COUNT(Area) FROM contact where Area = 1";
// execute the select query
$AFC = execute_db($sql, $conn);
while ($count_row = fetch_row_db($AFC)){
$AFC_records = $count_row[0];
} // end while

**************************************

Line 3 - Is <Area> the field name in the table?

Line 3 - Is <contact> the table name?

Lines 6-8 - Is <AFC> a value in field <Area>?

Thanks!

Rob

 

[alpha2zee]

Yes, those are field and table names. Note that in the = check, the right side doesn't have any quote marks because it's a numerical comparison in the example. Otherwise, it would be something like Name = 'John'.

The AFC gets a 'resource' that holds the results of the query. In general, the results are multiple arrays (each 'positive' record is a row in the results). The fetch_row_db command gets the arrays (rows) one by one. Thus, you can use AFC even if your query is entirely different.

One should flank table names and field names with backticks (`), and as mentioned before, values with single quote marks (') unless it is a number. The $sql in the example is better written as -
[pre]
$sql = "SELECT COUNT(`Area`) FROM `contact` WHERE `Area` = 1";
[/pre]

 

[DebbieS]

Thanks alpha2zee -- you beat me to it!

 

[nassausky]

I am having another idea in regards to this topic.. Before I work it out.. Does anyone know how to echo/diplay the letter "A" from it's decimal equivalent?

 

[billthecat]

Do you mean "A = 1", "B = 2" , etc, etc?

Assuming that, I don't know if there is a way to do it automatically, I would just write it out as an array.

<?php
$arr = array(
"A" => 1,
"B" => 2,
"C" => 3,
"D" => 4,
"E" => 5,
"F" => 6,
"G" => 7,
"H" => 8,
"I" => 9,
"J" => 10,
"K" => 11,
"L" => 12,
"M" => 13,
"N" => 14,
"O" => 15,
"P" => 16,
"Q" => 17,
"R" => 18,
"S" => 19,
"T" => 20,
"U" => 21,
"V" => 22,
"W" => 23,
"X" => 24,
"Y" => 25,
"Z" => 26);


echo $arr["Z"]; // Displays 26

echo $arr["M"]; // Displays 13
?>

 

[twinkers]

Linux - RedhatEnt5
Php - 5
Dada - 4.2

Hi, just trying to figure out the drop down menu search mentioned by debbie in post 7. I have it set up in the header file and when going to the page it appears and seems to work but the results still come back with everything. Basically I am trying to make a drop down that will search by the supervisors name which for now there are only 2. Chow and Evans and they are in the supervisors column of a table called thesis_pubs. This is what I've put so far.

<form method="POST" action="<? echo $dadabik_main_file; ?>?table_name=<?php echo urlencode($table_name); ?>&page=0&function=search&execute_search=1" enctype="multipart/form-data">
Search by Supervisor
<input name="supervisor_select_type" type="hidden" value="is_equal">
<select name="supervisor">
<option value="Chow">Chow</option>
<option value="Evans">Evans</option>
</select>
<input type="submit" class="submit" style="font-size:11px;" value="Find">
</form>

When I pick one and click find, every record shows. Anyone shed some light?

Thanks,
Stan

 

[twinkers]

I kind of figured it out myself but the only problem now is it only returns 12 records when I know there is 14.

<form method="POST" action="index.php?table_name=thesis_pubs&function=search&execute_search=1" enctype="multipart/form-data">
Search by Supervisor
<input name="supervisor__select_type" type="hidden" value="contains">
<select name="supervisor">
<option value=""></option>
<option value="Chow">Chow</option>
<option value="Evans">Evans</option>
</select>
<input type="submit" class="submit" style="font-size:11px;" value="Find">
</form>

 

[twinkers]

Never mind I got it working. There was a - in the chow name that I didnt post for confidentiality resons and was messing it up.