publish/unpublish records

[amwassil]

I know this is really a mySQL question, but I have been searching unsuccessfully for several hours to find an answer and I suspect you probably know the answer.

I have two types of queries, one simply lists all the records and displays certain fields from those records. Earlier today, I sent you a copy of the script after modifying it to try to get the Dataface valuelists picked up in the query per your instructions.

The second query is a search based on variables entered by the user, such as keywords and dates.

I have added another field to the table to enable the authors to indicate whether or not an article is complete and should be published. If the field is set to "DoNot" then the article is not published. If set to "Do" the article is published.

I've added a WHERE Publish="Do" in my query to restrict the results to those records with the field set to "Do" and this works fine in the first type of query. Here is the query:

$res = mysql_query('SELECT * FROM Enhancements WHERE Publish="Do" ORDER BY IWSys') or die(mysql_error());

The second type of query already has a "Where" clause in it:

$query = "select * from Enhancements WHERE Integrated like \"%$trimmed%\" order by IWSys";

My problem is I can not figure out how to add a second "where" to restrict the results to published articles only. I have tried the following without success:

..."select * from Enhancements WHERE Publish="Do" AND WHERE Integrated like...
..."select * from Enhancements WHERE Publish="Do" AND Integrated like...
..."select * from Enhancements WHERE (Publish="Do" AND Integrated like...)

Can you help me out with this, please. Thank you.

Michael Wassil

[shannah]

Hi Michael,

An SQL query has either 0 or 1 where clauses. If there is a where clause, then you can use AND and OR to concatenate multiple conditions together.

Therefore, given your SQL queries given:
..."select * from Enhancements WHERE Publish="Do" AND WHERE Integrated like...
..."select * from Enhancements WHERE Publish="Do" AND Integrated like...
..."select * from Enhancements WHERE (Publish="Do" AND Integrated like...)

The the last two should be OK. The first one is incorrect. If you are experiencing errors with the last two, then something else must be wrong. (I'm not sure if it is just the way that you pasted it, but The double quotes around "Do" would cause a parse error since you are already using double quotes around the query. If you use double quotes around the query, use single quotes around 'Do').

Sometimes, I will first place my sql query into a variable so that, in the case of failure I can print out the query and I'll be able to see what is going wrong (in case the query isn't rendering the way I wanted it to).

e.g.

Code: Select all

$sql = "select * from Enhancements WHERE Publish='Do' AND Integrated LIKE '%Query%'";
$res = mysql_query($sql, $db);
if ( !$res ){
    trigger_error(mysql_error($db).$sql, E_USER_ERROR);
    exit;
}


Just to give you the equivalent using the Dataface API, you would do:

Code: Select all

$query = array('Publish'=>'=Do', 'Integrated'=>'Some text');
    // Notice that I prepended an '=' sign to the 'Do' query
    //  This forces an exact match.  Otherwise it will use LIKE
    // The above query will result in sql like:
    //  select * from Enhancements WHERE Publish = 'Do' AND Integrated LIKE '%Some text%'
$records =& df_get_records('Enhancements', $query);


Hope this helps.

[amwassil]

Again, Steve, thank you very sincerely. I am a VERY happy camper, indeed!

The publish/unpublish field is working in both types of queries AND the valueslist values are displaying correctly as well.

Michael Wassil

[shannah]

Glad to hear the app is working for you Michael. If possible, when you get a public url, I'd like to take a look.

Best regards

Steve

[amwassil]

You've been a great help, Steve. The company I'm working with on this project is quite impressed with the Dataface backend functionality. With the sole exception of not being able to reorder the fields in List display, Dataface has fulfilled all the desired requirements. The front end is basically just to demonstrate that the data entered into Dataface can be displayed in different ways using different display criteria that they specified.

I can't say that it's 100% sure just yet, but at this point in time it looks very likely that Dataface will be adopted as the backend management system for this project and possible some others in the future as well. If indeed this happens I will be able to give you the URL for the frontend so you can see what you helped me to accomplish. The time frame for implementation is the next week or two.

I'm located in North Vancouver and the company I'm working with is located in Vancouver. So may I offer to buy you a brew or two at the pub of your choice! You can check out my website at http://www.telenexet.com

Michael Wassil