delete value from database using link

I have a page that shows comments, "comments.php", and I include the page on any other page that I want comments to show. I am trying to implement a way to delete comments if needed. Each comment has an auto-increment "commentID". Right now I'm using an action, and then just using a link to call the action.

When I hover over the link, the URL looks correct, but when I click it, the page refreshes and nothing happens. Any ideas?

Action:

if ($_POST['action'] == 'delete') {
    $sql = "delete from " . $db_prefix . "comments where commentID = " . (int)$_GET['id'];
    mysql_query($sql) or die('error deleting user: ' . $sql);
header('Location: ' . $_SERVER['HTTP_REFERER']);
}

Show comments and show link to delete: (unnecessary code has been left out)

echo '<a href="/comments.php?action=delete&id=' . $result['commentID'] . '">delete</a> 

What am I doing wrong?

via Chebli Mohamed

How to extract certain xml strings from a column in sql server 2008 and make a new table and columns using it's data?

Hi I have a table named 'BOOKING' and it has 4 columns, the 4th column's data came from a trigger which will have the xml string. Here's the sample data on the 4th column;

<i guest_id="PEK150700019-001" updated_by="sherwin" update_dt="2015-08-11T13:59:09.550" place_birth="SHANGHAI"/>

What I need is to extract the guest_id value, the updated_by vlaue, the update_dt value and so on: in other words, the xml string can have more strings in xml format.

I want to extract those strings from the 4th column and make a new table for it. Here is the sample output;

=================================================================
|guest_id          |   updated_by     |     update_dt           |
|==================|==================|=========================|
|PEK150700019-001  |  sherwin         |  2015-08-11T13:59:09.550|
|------------------|------------------|-------------------------|
|PEK150700019-002  |  wane            |  2015-09-11T13:00:09.540|
|==================|==================|=========================|

So Far, on my research I have done something like this;

  declare @txt varchar(max)
    set @txt ='<i guest_id="PEK150700019-001" updated_by="sherwin" update_dt="2015-08-11T13:59:09.550" place_birth="SHANGHAI"/>'

    SELECT  [updated_by] = SUBSTRING(audit_val,
    (CHARINDEX('updated_by',audit_val,1)+12),
    (CHARINDEX('"',audit_val,(CHARINDEX('updated_by',audit_val,1)+12)))-(CHARINDEX('updated_by',audit_val,1)+12)),''))

That code extracts the xml string 'updated_by' and making a new column for a table.

Can I ask for a help ? Is there a way to extract those xml strings and make a new columns for a new table on it ? I mean an easier way to do that without using substring or charindex like the one on my sample output? PLEASE HELP thanks in advance :)

via Chebli Mohamed

Why is "update foo ... where bar is null" letting multiple callers claim the same row?

I have a fairly basic query:

UPDATE the_table SET col1=[something], col2=[something else] WHERE col1 IS NULL AND col2 IS NULL;

Immediately after issuing the query, the caller does:

SELECT col3 FROM the_table where col1=[something], col2=[something else];

Unfortunately, concurrent callers are claiming the same row.

I'd rather not do a SELECT FOR UPDATE, because the [select, update, select] would involve three rpcs to the database instead of two (which is bad enough.)

I gather that some dialects of sql allow UPDATE the_table WITH(UPDLOCK), but mine (galera/MySQL) does not. I find it appalling that I'd have to go through this many DB hits to execute such a basic concept. I find that most of my searching efforts end on pages that discuss dialects that DO support UPDLOCK.

Where does it go from here?

via Chebli Mohamed

SQL Query for results from 7 days ago

I have a SQL query that I need to update to pull results from exactly 7 days ago. At the moment the query is looking at result 7 days and before. I can't seem to find a character that changes the > to an equals.

SELECT
   distinct(cl.RIID_),
   cl.EMAIL_ADDRESS_  
FROM
   $A$ cl  
JOIN
   $C$ bro 
      ON cl.EMAIL_ADDRESS_ = bro.EMAIL_ADDRESS_   
JOIN
   $D$ cms 
      ON cms.SKU = bro.ITEM  
WHERE
   cl.EMAIL_DELIVERABILITY_STATUS_ = 'D'   
   AND cl.EMAIL_PERMISSION_STATUS_ = 'I'   
   AND (
      cms.CATEGORYNAME = 'Desktop Systems' 
      OR cms.CATEGORYNAME = 'Refurbished Laptops & Tablets' 
      or cms.CATEGORYNAME = 'Laptops & Notebooks' 
   ) 
   AND (
      trunc(bro.ACTIVITY_DATE)
   ) >= (
      Trunc(Cast(From_Tz(Cast(Sysdate As Timestamp ), 'US/Pacific') At Time Zone 'Australia/Sydney' As Date)) - 7
   )

Thanks for your help!

via Chebli Mohamed

Can this raw SQL be written using the Rails Active Record Query Interface? Should it be?

In my Rails 4 app I make a fairly simple search for one of my models using the following SQL 'OR' statements. It works fine. Is there any way (and reason) to achieve this without raw SQL using the Rails Active Record Query Interface?

Activity.where("
    user_id = ? OR 
    category = ? OR 
    (secondary_id = ? AND secondary_model = ?) OR 
    (tertiary_id = ? AND tertiary_model = ?)",
    user_id, "Announcement", user_id, "user", user_id, "user"
).uniq

via Chebli Mohamed

How to get the union of a different number of datasets? [SAS]

I'm running a code, almost in an automatic way. I just need to replace one value, and run. But there's one part where i have to do it 'by hand'.

It's the following code:

PROC SQL; CREATE TABLE DDATA.SUS_151_ALL AS 
 SELECT * FROM 
 (SELECT * FROM 
 DDATA.RFN_ID673 
 UNION
 SELECT * FROM 
 DDATA.RPFN_ID472 
 UNION
 SELECT * FROM 
 DDATA.RPFN_ID553);
 QUIT;

In this case, the clients i want to get the union are here:

 PROC SQL;
SELECT EN FROM DDATA.E5P_151; 
SELECT COUNT(*) FROM DDATA.E5P_151;
QUIT;

Here, i obtain the following results:

673
472
553 
---page break--
3

So, i want something that automatically would read the 3 datasets i wanted to get the union and create the table DDATA.SUS_151_ALL.

I have other clients where i have 8 id's to join, and having to do it by hand 100 times takes me some time. i would want just to replace the 151 , the source.

For example, for other client, let's say id=1000 like this one:

3
7
9
12
16
77
991
1028

I would want a program that would run this:

PROC SQL; CREATE TABLE DDATA.SUS_1000_ALL AS 
     SELECT * FROM 
     (SELECT * FROM 
     DDATA.RFN_ID3
     UNION
     SELECT * FROM 
     DDATA.RPFN_ID7 
     UNION
     SELECT * FROM 
     DDATA.RPFN_ID9
     UNION
     SELECT * FROM
     DDATA.RPFN_ID12
     UNION
     SELECT * FROM
     DDATA.RPFN_ID16
     UNION
     SELECT * FROM
     DDATA.RPFN_ID77
     UNION
     SELECT * FROM
     DDATA.RPFN_ID991
     UNION
     SELECT * FROM
     DDATA.RPFN_ID1028);
     QUIT;

Is this possible? Could you give me some hints?

via Chebli Mohamed

How to delete all but most recent row where other columns are equal

I have a MySQL table, an excerpt of which is here:

http://ift.tt/1WnpK6F

I need to keep the most recent row for each row where the assoc_case, document, and participant are the same. For example, I only want to keep row 136 out of rows 133-136.

I'm working from this, but can't seem to adapt it for my needs:

SELECT id, assoc_case, participant, document, MAX(created) FROM `table` GROUP BY created, assoc_case, participant, document

via Chebli Mohamed