How to speed up the query in PostgreSQL

I have DB in PostgreSQL with a big data (now it is somewhere around 46 GB and the db will keep growing). I created indexes on often used columns and adapted the config file:

shared_buffers = 1GB
temp_buffers = 256MB
work_mem = 512MB

But this query is still slow:

select distinct us_category_id as cat, count(h_user_id) as res from web_hits 
inner join users on h_user_id = us_id 
where (h_datetime)::date = ('2015-06-26')::date and us_category_id != ''
group by us_category_id

Explain Analyze:

HashAggregate (cost=2870958.72..2870958.93 rows=21 width=9) (actual time=899141.683..899141.683 rows=0 loops=1)

Group Key: users.us_category_id, count(web_hits.h_user_id)
-> HashAggregate (cost=2870958.41..2870958.62 rows=21 width=9) (actual time=899141.681..899141.681 rows=0 loops=1)

Group Key: users.us_category_id
 -> Hash Join (cost=5974.98..2869632.11 rows=265259 width=9) (actual time=899141.679..899141.679 rows=0 loops=1)

Hash Cond: ((web_hits.h_user_id)::text = (users.us_id)::text)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
-> Seq Scan on web_hits (cost=0.00..2857563.80 rows=275260 width=7) (actual time=899141.676..899141.676 rows=0 loops=1)
Filter: ((h_datetime)::date = '2015-06-26'::date)

Rows Removed by Filter: 55051918
-> Hash (cost=4292.99..4292.99 rows=134559 width=10) (never executed)
-> Seq Scan on users (cost=0.00..4292.99 rows=134559 width=10) (never executed)
Filter: ((us_category_id)::text <> ''::text)

"Planning time: 1.309 ms"
"Execution time: 899141.789 ms"

Date is changed. How can I speed up the query?

Table and index creating

CREATE TABLE web_hits (
  h_id integer NOT NULL DEFAULT nextval('w_h_seq'::regclass),
  h_user_id character varying,
  h_datetime timestamp without time zone,
  h_db_id character varying,
  h_voc_prefix character varying,
  ...
  h_bot_chek integer, -- 1-бот...
  CONSTRAINT w_h_pk PRIMARY KEY (h_id)
);
ALTER TABLE web_hits OWNER TO postgres;
COMMENT ON COLUMN web_hits.h_bot_chek IS '1-бот, 0-не бот';

CREATE INDEX h_datetime ON web_hits (h_datetime);
CREATE INDEX h_db_index ON web_hits (h_db_id COLLATE pg_catalog."default");
CREATE INDEX h_pref_index ON web_hits (h_voc_prefix COLLATE pg_catalog."default" text_pattern_ops);
CREATE INDEX h_user_index ON web_hits (h_user_id text_pattern_ops);

 CREATE TABLE users (
  us_id character varying NOT NULL,
  us_category_id character varying,
  ...
  CONSTRAINT user_pk PRIMARY KEY (us_id),
  CONSTRAINT cities_users_fk FOREIGN KEY (us_city_home)
      REFERENCES cities (city_id),
  CONSTRAINT countries_users_fk FOREIGN KEY (us_country_home)
      REFERENCES countries (country_id),
  CONSTRAINT organizations_users_fk FOREIGN KEY (us_institution_id)
      REFERENCES organizations (org_id),
  CONSTRAINT specialities_users_fk FOREIGN KEY (us_speciality_id)
      REFERENCES specialities (speciality_id),
  CONSTRAINT us_affiliation FOREIGN KEY (us_org_id)
      REFERENCES organizations (org_id),
  CONSTRAINT us_category FOREIGN KEY (us_category_id)
      REFERENCES categories (cat_id),
  CONSTRAINT us_reading_room FOREIGN KEY (us_reading_room_id)
      REFERENCES reading_rooms (rr_id)
);
ALTER TABLE users OWNER TO sveta;
COMMENT ON COLUMN users.us_type IS '0-аноним, 1-читатель, 2-удаленный';

CREATE INDEX us_cat_index ON users (us_category_id);
CREATE INDEX us_user_index ON users (us_id text_pattern_ops);

No response from server : faulty php script?

So I'm continuing to work on this login.php script

<?php

$link = new MySQLi (localhost, Maelhann, userpassword, Logindata2256 ) ;

if ( mysqli_connect_error() ){

$logmessage = 'MySQL error : ' . mysqli_connect_error() ; 
die('could not connect to database');
}

$Email = $_POST['Email'];
$Password = $_POST['Password'];

 if ( int mysqli_result::$num_rows() == 1 ){

$_SESSION["Email"];
$_SESSION["Password"];
header("location:Newaccount.html");
 }
else{
echo "wrong username or password" ;

}

?>

but all I get when trying to log in is a blank page... any Idea where I could have failed?

How can I make a pseudo-dynamic Stored procedure / CTE

I need to write a sql query where I have a group of students who are in a specific program and display their completion status for each course in the program. The problem is that the program is a parameter to go into the query, and each time I run it (with a different program) each program has a different set of courses associated with it.

So, from a table that relates Students to programs. (StuPRograms) Many to many relationship between Programs and Courses, and then I get a result from another table (Enrollments) that joins Students to Courses. The rows are students while the columns are pivots using the courses, and the cells are the result of if that student has completed the course.

I could do a normal SQL Select with piles of calculated columns. (the columns being the student, the name of the first course, the status of the student in the first course, name of 2nd course, status in 2nd course.. etc..) but that would be crazy slow.

I've used TSQL to generate the data with a dynamic pivot, but because it has dynamic columns I can't put it in a Stored Procedure. (Because the SP needs to generate a constant schema.) So, I'm thinking if I limit my results to only look at a 10 course columns that would give it a static schema, so I can put it in a stored proc.. Effectively I'm looking for a better performing solution then to do 20 column sql statements for each StuProgram row returned.

MySQL Order by + Group by drastically slow down the query

I have the following query that, despite its complexity, performs well in the following shape:

SELECT 

`listings`.*, 
`auth_agents`.*, 
`symbols`.`code` AS `symbol_code`,
`symbols`.`text` AS `symbol_text`,
`symbols`.`position` AS `symbol_position`,
GROUP_CONCAT(DISTINCT IFNULL(`categories_countries`.`translation`, `c`.`name`) ORDER BY CASE WHEN `c`.`category_id` = 106 THEN 1 ELSE `c`.`category_id` END) as `category_names`,
`pv_1`.`value` AS `transaction_type_name`,
`pv_2`.`value` AS `property_type_name`,
`listings_statuses`.`name` AS `status`,
CONCAT(`pv_3`.`value`, 'property/', `listings`.`slug`, '/', `listings`.`listing_id`) AS `url`,
price_range(`listings`.`price_sales_min`, `listings`.`price_sales`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_sales_range`,
price_range(`listings`.`price_lettings_min`, `listings`.`price_lettings`, `symbols`.`code`, `symbols`.`position`, `listings`.`listings_price_qualifier_id`) AS `price_lettings_range`,
summary(`listings`.`description`, `listings`.`summary`) AS `summary`,
short_title(`categories`.`name`, `categories`.`title`, `pv_1`.`value`, `listings`.`city`, `listings`.`region`, `listings`.`area`) AS `short_title`,
IF(`listings`.`listings_price_qualifier_id` = 2, TRUE, FALSE) AS `POA`,
IF((SELECT `listings_categories`.`listing_id` FROM `listings_categories` WHERE `listings_categories`.`listing_id` = `listings`.`listing_id` AND `listings_categories`.`category_id` = 124 LIMIT 1) IS NOT NULL, TRUE, FALSE) AS `serviced_office`,

GROUP_CONCAT(DISTINCT CONCAT_WS('|', `auth_agents_staff`.`auth_agents_staff_id`, `auth_staff`.`f_name`, `auth_staff`.`l_name`, `auth_staff`.`photo`)) AS `agents_staff`,
(SELECT CONCAT('http://ift.tt/1GIT2RV', `listings`.`auth_agent_id`, '/', `listings_media`.`filename`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` ORDER BY `default` DESC LIMIT 1) AS `image`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 1) AS `image_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 3) AS `pdf_count`,
(SELECT COUNT(`listings_media`.`listings_media_id`) FROM `listings_media` WHERE `listings_media`.`listing_id` = `listings`.`listing_id` AND `listings_media`.`listings_media_type_id` = 4) AS `floor_plan_count`,


distance(`listings`.`lat`, `listings`.`lng`, 51.5073500, -0.1277600) AS `distance` 

FROM `listings` 
JOIN `listings_coords` ON `listings_coords`.`listing_id` = `listings`.`listing_id` 

JOIN `auth_agents` ON `listings`.`auth_agent_id` = `auth_agents`.`auth_agent_id` 
JOIN `branches` USING (`branch_id`) 
LEFT JOIN `listings_auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `auth_agents_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `listings_auth_agents_staff`.`auth_agents_staff_id` = `auth_agents_staff`.`auth_agents_staff_id` 
LEFT JOIN `auth` AS `auth_staff` ON `listings_auth_agents_staff`.`listing_id` IS NOT NULL AND `auth_agents_staff`.`auth_agents_staff_id` IS NOT NULL AND 
`auth_staff`.`auth_id` = `auth_agents_staff`.`auth_id` 

JOIN `parameters_values` AS `pv_1` 
ON `pv_1`.`parameter_id` = 2 AND `pv_1`.`country_id` = 223 AND `pv_1`.`key` = `listings`.`transaction_type` 

JOIN `parameters_values` AS `pv_2` 
ON `pv_2`.`parameter_id` = 1 AND `pv_2`.`country_id` = 223 AND `pv_2`.`key` = `listings`.`property_type` 

JOIN `parameters_values` AS `pv_3` 
ON `pv_3`.`parameter_id` = 5 AND `pv_2`.`country_id` = 223 

JOIN `countries` ON `listings`.`country_id` = `countries`.`country_id` 
JOIN `countries_symbols` ON `countries`.`country_id` = `countries_symbols`.`country_id` 
JOIN `symbols` ON `countries_symbols`.`symbol_id` = `symbols`.`symbol_id` 

JOIN `listings_units` ON `listings`.`listings_unit_id` = `listings_units`.`listings_unit_id` 

JOIN `listings_units` AS `user_units` ON `user_units`.`listings_unit_id` = 2 


JOIN `listings_statuses` ON `listings`.`listings_status_id` = `listings_statuses`.`listings_status_id` 
LEFT JOIN `listings_statuses_countries` ON `listings_statuses`.`listings_status_id` = `listings_statuses_countries`.`listings_status_id` 
AND `listings_statuses_countries`.`country_id` = '223' 

JOIN `listings_categories` ON `listings_categories`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` ON `categories`.`category_id` = `listings_categories`.`category_id` 

JOIN `listings_categories` AS `lc` ON `lc`.`listing_id` = `listings`.`listing_id`  
JOIN `categories` AS `c` ON `c`.`category_id` = `lc`.`category_id` 
LEFT JOIN `categories_countries` ON `c`.`category_id` = `categories_countries`.`category_id` AND `categories_countries`.`country_id` = '223' 

LEFT JOIN `listings_lettings`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings`.`listing_id` = `listings`.`listing_id` 
LEFT JOIN `listings_lettings_rent_frequencies`  
ON (`listings`.`transaction_type` = 2 OR `listings`.`transaction_type` = 3) AND `listings_lettings_rent_frequencies`.`listings_lettings_rent_frequency_id` = `listings_lettings`.`listings_lettings_rent_frequency_id` 

LEFT JOIN `listings_lettings_rent_frequencies` AS `llrf` ON `llrf`.`listings_lettings_rent_frequency_id` = 2 
WHERE `listings`.`active` = 1 AND `listings`.`published_flag` = 1 AND 
`listings`.`listings_status_id` = 1 AND
`listings`.`lat` BETWEEN 51.333436956522 AND 51.681263043478 AND 
`listings`.`lng` BETWEEN -0.40717687879372 AND 0.15165687879372 AND 
st_within(`listings_coords`.`coords`, @poly) AND 

IF (`listings`.`transaction_type` = 2 || `listings`.`transaction_type` = 3, 
`listings`.`price_lettings` * `listings_lettings_rent_frequencies`.`months` >= `llrf`.`months` * 500,
TRUE
) AND 
IF(`listings_units`.`meters` IS NULL, TRUE, `listings`.`max_size` * `listings_units`.`meters` >= 100 * `user_units`.`meters`) AND 
`listings`.`country_id` = 223 

GROUP BY `listings`.`listing_id`  
ORDER BY `listings`.`created_at` 
LIMIT 10; 

As you can see, the first query grabs the polygon which then is used in polygon search through spatial function. The query executes in aprox 0.06sec.

However adding any ORDER BY clause results in a drastic performance drop and ends up executing in 17 seconds.

Now - I've done some research and I know this might be to do with either clustered indexes or just adding the right indexing in general, but after hours spent trying to either return just the ID's and then run another query to get the data or simply getting rid of the GROUP BY and removing the joins (which seemed to have worked at first but then another performance issues appeared when I joined a few tables to do a price search) - I gave up.

I tried to run a query without grouping and then group the results, like:

SELECT * FROM ( *the query* ) AS results GROUP BY listing_id

But I got pretty much the same results.

Is there a way to optimise the query so I could do both grouping and sorting?

Here is the EXPLAIN:

    |  1 | PRIMARY            | countries                          | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | Using index; Using temporary; Using filesort                              |
    |  1 | PRIMARY            | user_units                         | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses                  | const  | PRIMARY                                                                           | PRIMARY                              | 4       | const                                                            |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_statuses_countries        | const  | PRIMARY,country_id                                                                | PRIMARY                              | 8       | const,const                                                      |    1 | Using index                                                               |
    |  1 | PRIMARY            | countries_symbols                  | ref    | PRIMARY,symbol_id                                                                 | PRIMARY                              | 4       | const                                                            |    1 | Using index                                                               |
    |  1 | PRIMARY            | symbols                            | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.countries_symbols.symbol_id                           |    1 | NULL                                                                      |
    |  1 | PRIMARY            | pv_2                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    2 | NULL                                                                      |
    |  1 | PRIMARY            | pv_1                               | ref    | parameter_id,country_id                                                           | parameter_id                         | 8       | const,const                                                      |    3 | NULL                                                                      |
    |  1 | PRIMARY            | pv_3                               | ref    | parameter_id                                                                      | parameter_id                         | 4       | const                                                            |    4 | NULL                                                                      |
    |  1 | PRIMARY            | listings                           | range  | SEE BELOW                                                                         | compound                             | 20      | NULL                                                             | 2594 | Using index condition; Using where; Using join buffer (Block Nested Loop) |
    |  1 | PRIMARY            | listings_units                     | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listings_unit_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_coords                    | eq_ref | PRIMARY,coords                                                                    | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_agents                        | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.auth_agent_id                                |    1 | NULL                                                                      |
    |  1 | PRIMARY            | branches                           | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.branch_id                                    |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_categories                | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | lc                                 | ref    | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | listings_auth_agents_staff         | ref    | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings.listing_id                                   |    1 | Using index                                                               |
    |  1 | PRIMARY            | categories                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_categories.category_id                       |    1 | NULL                                                                      |
    |  1 | PRIMARY            | c                                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.lc.category_id                                        |    1 | NULL                                                                      |
    |  1 | PRIMARY            | auth_agents_staff                  | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.listings_auth_agents_staff.auth_agents_staff_id       |    1 | Using where                                                               |
    |  1 | PRIMARY            | auth_staff                         | eq_ref | PRIMARY                                                                           | PRIMARY                              | 4       | cp_staging.auth_agents_staff.auth_id                             |    1 | Using where                                                               |
    |  1 | PRIMARY            | categories_countries               | eq_ref | PRIMARY,categories_countries_country_id_foreign                                   | PRIMARY                              | 8       | cp_staging.lc.category_id,const                                  |    1 | NULL                                                                      |
    |  1 | PRIMARY            | listings_lettings                  | ref    | listings_lettings_listing_id_foreign                                              | listings_lettings_listing_id_foreign | 4       | cp_staging.listings.listing_id                                   |    1 | Using where                                                               |
    |  1 | PRIMARY            | listings_lettings_rent_frequencies | eq_ref | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | cp_staging.listings_lettings.listings_lettings_rent_frequency_id |    1 | Using where                                                               |
    |  1 | PRIMARY            | llrf                               | const  | PRIMARY,listings_lettings_rent_frequencies_id_months                              | PRIMARY                              | 4       | const                                                            |    1 | Using where                                                               |
    |  5 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  4 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  3 | DEPENDENT SUBQUERY | listings_media                     | ref    | listings_media_listing_id_foreign,listings_media_listings_media_type_id_foreign   | listings_media_listing_id_foreign    | 4       | func                                                             |    1 | Using where                                                               |
    |  2 | DEPENDENT SUBQUERY | listings_categories                | eq_ref | PRIMARY,listings_categories_category_id_foreign                                   | PRIMARY                              | 8       | func,const                                                       |    1 | Using index                                                               |

5th column value for listings table (pulled out so that format of above table could be preserved) PRIMARY,listings_former_id_unique,listings_price_sales_index,listings_price_sales_min_index,listings_price_lettings_index,listings_price_lettings_min_index,listings_country_id_foreign,listings_agent_id_foreign,listings_listings_status_id_foreign,listings_listings_price_qualifier_id_foreign,listings_listings_unit_id_foreign,listings_listings_tenure_type_id_foreign,agents_staff_id,listings_listings_class_id_foreign_idx,listings_listings_location_type_id_foreign_idx,listings_listings_branch_id_foreign_idx,latlng,listings_city,listings_created_at,listings_country_created_at,listings_transaction_type,listings_listing_id_transaction_type,listings_listings_coords_id,listings_created_at_listing_id,compound

How to open a URL in an SSRS report in a new window? While the URLs keep changing in every record

I want to be able to open links in a new window in my SSRS report. The problem is these links are not the only things present in the record, and there is other data present. I keep seeing examples where they are considering only a link to be present in the record with no other data.

Also the data in the record includes HTML tags and I checked the option that says "HTML - Interpret HTML tags as styles" on the placeholder properties window.

Example of data in the record:

Hello! Welcome to google, the most used search engine. 
Click on www.google.com to go the website

So when I click on the www.google.com URL, it should open in a new window.

Edit: The URLs keep changing in every record.

I want more example on Optimization SQL Query

I want more example on Optimization SQL Query

"Advanced" SQL tutorial (t sql preferred)

So, I've recently started learning SQL, but I've hit a bit of a roadblock: nothing seems to be offered online besides the most basic topics. I used the tutorial at khan academy and GalaXQL but there doesn't really seem to be a site with more advanced problems than those contained within the previous two sites. Does anyone know of a place I can get used to more advanced queries / challenge myself further? I'm working within a T SQL environment, so anything related to that would be great, but I'll be happy with anything I can get.

OrientDB Time Search Query

In OrientDB I have setup a time series using this use case. However, instead of appending my Vertex as an embedded list to the respective hour I have opted to just create an edge from the hour to the time dependent Vertex.

For arguments sake lets say that each hour has up to 60 time Vertex each identified by a timestamp. This means I can perform the following query to obtain a specific desired Vertex:

SELECT FROM ( SELECT expand( month[5].day[12].hour[0].out() ) FROM Year WHERE year = 2015) WHERE timestamp = 1434146922

The first part of this question is whether there are any advantages/disadvantages to storing the timestamp as a property of the Vertex (as above query) versus storing it as the edge (or an edge property). In which case I think the query would be:

SELECT expand( month[5].day[12].hour[0].out('1434146922') ) FROM Year WHERE year = 2015

Although this looks more elegant I have 2 concerns; the overhead of creating new edge types. The flexibility if you don't actually know the exact timestamp.

The second part of the question relates to the fact that once I have isolated the individual Vertex based on time, this is only the head of a hierarchal tree of Vertex.

I could of course get the @rid from the above query and then construct a new query but I was wondering how I could adapt the above to do it all in one. For example assume there is a boolean property called active in all the hierarchal Vertexes. How can I get all the Vertex that are true?

Unable to grant Postgres permissions.

I'm using the following set of commands on Postgres 9.1 that has tables in abc.onlySchema.

create user myuser with password '1234';
create role mygroup inherit;

grant all privileges on database abc to mygroup;
grant all privileges on all tables in schema onlySchema to mygroup;
grant all privileges on all sequences in schema onlySchema to mygroup;
grant all privileges on all functions in schema onlySchema to mygroup;

grant mygroup to myuser;

The commands are entered by superuser myadmin. When I log in as myuser, I'm unable to select from tables I know exist and the command \dt returns nothing. Set role mygroup changes nothing. Am I missing some magic command that tells Postgres that I was serious when I issued the above commands. Perhaps a FLUSH PRIVILEGES-like command.

Btw, when I issue \dp as my myadmin user, the following appears {...,myuser=arwdDxt/myadmin} and I have set search_path = onlySchema; AND alter role myuser set search_path to onlySchema;. Still nothing.

Extracting event order from SQL table

I have a SQL table similar to this:

+----+-------+--------+-------+-------+
| rd | ts_in | ts_out | sens  | devid |
+----+-------+--------+-------+-------+
|  1 |     0 |   1577 |     2 |    2  |
|  1 |   229 |   1549 |     1 |    2  |
|  1 |   123 |   1322 |     0 |    2  |
+----+-------+--------+-------+-------+

Where a data logger ends up storing it's data.

This data logger is stored underground and it have two inductive devices that detect when a car walks on top of it, one on it's beginning, another on it's end. It also have a device between the two sensors which weights the car axis on top of it.

When an inductive device detects a car is on top of it, the datalogger takes a timestamp of it's internal clock (ts_in) and waits until the sensor is deasserted, by then it takes another snapshot (ts_out) and it inserts a line on the table containing the datalogger id (devid), the sensor which triggered (1 = front, 2 = rear) the event, and the related timestamps. It also sets the rd field to a per-sensor unique counter (but it's not a per-device, as you can notice in the example).

The weight device also have similar behavior but it stores sens=0 and other data not present in the example (but it's ts_in and ts_out works the same).

One can think of I would always get data like this: sens 1 -> sens 0 -> sens 2 if the vehicle goes in some direction and: sens 2 -> sens 0 -> sens 1 if the vehicle goes in the opposite direction, but there are caveats:

a) the sensors may fail. The weight doesn't, so sometimes I can get only a sens 0 event, other times it gets it and one of the sensors but not the other.

b) I need to match the events around a sens0 entry: if I have a sens0 entry with a ts_out > sens1_tsout && sens2_tsout, I know those sens1/sens2 entries can't belong to this one.

In order for this information to be useful to me, I need to do a self join on the table and extract the following information:

• I need do determine, for every sens0 entries, if there's a pair of matching sens1 & 2 entries for which it's ts_out < sens0_tsout, and I also need to get the ts_in of the first sensor that hits (something in mysql like LEAST(a.ts_in, a.ts_out) AND GREATEST(a.ts_out, b.ts_out).

What I expect in output is something like (the column order is unimportant):

+------| --------- | ------------+
|devid | direction |  sens0_data |
+------| --------- | ------------+
| 2    |  *text*   |  *weigth*   |
+------| --------- | ------------+

Where text could be something like:

"forward" (i.e. tsin_sens11 > tsin_sens2 && tsout_sens2 > tsout_sens1);

"backward" (i.e.tsin_sens12 > tsin_sens1 && tsout_sens1 > tsout_sens2) "forward_rewind" (i.e. tsin_sens1 > tsin_sens2 && tsout_sens2 < tsout_sens1);

"backward_rewind" (i.e. tsin_sens2 > tsin_sens1 && tsout_sens1 < tsout_sens2);

NULL (i.e. there's no lines maching the above conditions, inclusive the case where there's no matching sens1/2 lines where ts_out < sens0 ts_out).

Basically, I need to prove, for every sens0 line, that I either:

• Have a nearby (in terms of ts_in & ts_out) pair of sens = 1 and sens = 2 lines;

• Having those, that the car entered on one direction (which I must differentiate) and left on the other, or that it went backwards.

• That, absent those conditions, I only have the sens = 0 data and NULL for the direction field.

Is that feasible in two or three inner joins or should I take a multiple query step?

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. - error

I got problem with second question, becasue i receive error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.". Can you help me?

Select P.Name, P.ProductCategoryID, PC.Name, Max(P.ListPrice) as MaxPrice
from SalesLT.Product as P
join SalesLT.ProductCategory as PC
on PC.ProductCategoryID=P.ProductCategoryID
where P.ListPrice=
    (select Max(ListPrice)
    from SalesLT.Product as P2
    where P2.ProductCategoryID=P.ProductCategoryID)
group by P.Name, P.ProductCategoryID, PC.Name
order by MaxPrice desc;

go

with sale_cte as 
    (Select PC.Name, P.ProductCategoryID, P.Listprice,
    ROW_NUMBER() over(partition by PC.Name order by P.Listprice) as RN
    from SalesLT.Product as P
    join SalesLT.ProductCategory as PC
    on PC.ProductCategoryID=P.ProductCategoryID)
Select PC.Name,
    (select *
    from sale_cte)
from SalesLT.ProductCategory as PC
wher RN=1

How to setup MS SQL for Lightswitch intrinsic deploy - exception occurred when building the database for the application

Question Sign in to vote 0 Sign in to vote VS2013 Community html project, SQL2014 Standard db

Does anyone have a good walkthrough of deploying with an intrinsic db? Deployment with external db works fine, but not with intrinsic.

F5 build works fine on localhost with external or intrinsic db, server deploy to IIS/SQL works fine with external db... just not with intrinsic db...

None of the docs I have found are real detailed about how to setup sql server to handle the intrinsic deployment.

Created the sql project, selected that in LS app properties. Do I create a DB on the SQL server or does LS do that? The Publish dialog on the database step says the admin account will be used to "create and update" the db. Have tried sql admin account with and without specifying a target db. Can the user account be the same as the admin account? Tried that both ways.

getting a count from 1 table given an input from another

I have 3 tables, filer_info, filer_persent, and persent_email connected via filer_info.filer_info_id=filer_persent.filer_info_id and filer_persent.persent_info_id=persent_email.persent_info_id

I want to find all rows where I have multiple type PRIMARY in the persent_email table (ie count > 1). And the only thing I want to return in the query is filer_info.filer_ident and the count.

This gives me every row, but I only want the data where filer_ident > 1 in the returned rows.

       select * from filer_info f
       inner join filer_persent fp on f.filer_info_id=fp.filer_info_id 
       inner join persent_email p on fp.persent_info_id=p.persent_info_id
       where fp.filer_persent_kind_cd = 'FILER' and     p.persent_email_kind_cd='PRIMARY'
       order by f.filer_ident

Way to eliminate DISTINCT in this query

This query returns the correct SUM, but its duplicating itself:

SELECT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T 

I can eliminate this behavior by adding DISTINCT.

SELECT DISTINCT [GrandTotal] = (total1 + total2)
FROM (SELECT(SELECT SUM([Total]) [total1] FROM [ooc].[RentalRevenue2] WHERE [CheckOut] >= '2015-01-01' AND [CheckOut] < '2015-06-30' AND [CampaignName] NOT LIKE '%Non-Cancellable%') as total1,
            (SELECT SUM([Total]) [total2] FROM [ooc].[Revenue2] WHERE [DateConfirmationReceived] >= '2015-01-01' AND [DateConfirmationReceived] < '2015-06-30' AND [CampaignName] LIKE '%Non-Cancellable%') as total2
      FROM [ooc].[Revenue2]) T 

I don't think this is the correct way of executing this SUM query.

SQL Select - How to show multiple columns based on field value?

I have been trying to put together an SQL query that shows one line for each record with the values from another field broken out into their own columns. How would I be able to show multiple columns and a single row for each record?

I have a table with data similar to the following sample:

+--------------+------------+---------------+
| Employee_Num | Client_Num | Deduction_Num |
+--------------+------------+---------------+
|         1305 |       1000 |             1 |
|         1305 |       1000 |            30 |
|         1312 |       1000 |             1 |
|         1320 |       1000 |             1 |
|         1320 |       1000 |            30 |
|         1323 |       1000 |            30 |
|         1323 |       1000 |             1 |
+--------------+------------+---------------+

I have attempted a union but the results still show multiple records for each employee. Here's what I have tried thus far:

SELECT Employee_Num, Client_Num, Deduction_1, Deduction_30
FROM ( SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE client_num = 1000
    AND Deduction_Num IN (1) 
UNION

    SELECT 
    Employee_Num,
    Client_Num, 
    Deduction_Num AS Deduction_1, 
    Deduction_Num AS Deduction_30
    FROM Employee_Deductions
    WHERE Client_Num, = 1000
    AND Deduction_Num IN (30)  
) AS Datum

WHERE Client_Num = 1000
ORDER BY Employee_Num

I would like this to be the desired result:

+--------------+------------+-------------+--------------+
| Employee_Num | Client_Num | Deduction_1 | Deduction_30 |
+--------------+------------+-------------+--------------+
|         1305 |       1000 |           1 |           30 |
|         1312 |       1000 |           1 |              |
|         1320 |       1000 |           1 |           30 |
|         1323 |       1000 |           1 |           30 |
+--------------+------------+-------------+--------------+

Any help would be appreciated.

Preferred way of data upgrade considering abstraction of data storage format

The core question is how to properly execute upgrade of data while applications versions differ in matter of data representation? E.g. for databases it is often to use SQL scripts to modify persisted data model. I can easily imagine similar way using XSL for XML and so on. But I would like to create abstraction of data persistence model in that way that upgrade method does not consider way of storing data - it is suffice that it can read previous version data and save new ones.

I tried doing such thing in C# like loading previous version of assembly containing data definition classes through reflection while having current version deployed with application. It is quite complex and tricky solution. Other option is to keep previous model classes redundantly with some version based naming conversion - this solution is easier as it gives full access to types at compile time.

My idea asking this question is, what is the proper solution of such upgrades of data?

Get start date of week using datepart

I have a table with 2 columns - Date, ItemsSold. I have the following query

Select datepart(week, [Date]) as WeekNo, SUM([ItemsSold]) as Total From [table] 
group by datepart(week, [Date])

This gives the following output

WeekNo   Total
3         20
4         30

What I actually need is an output as follows

WeekOf          Total
11 Jan 2015      20
18 Jan 2015      30

How do I get the start date of the week that datepart returns?

SQL Server Security Logins

On our Development and test servers I have added a windows authentication login for a new employee. I need this login to work for all databases on the server and to allow for them to be able to create, add, etc. I know if I set the server role to sysadmin it will allow all the permissions but is this the proper way to set up the user? Should there be some other role that I should map the user to and grant permissions through that role?

Translate plain sql to sql alchemy

I have this generic query, that i want to reproduce in sql alchemy

select id, last_year, ((atr_a * 0.3 + atr_b * 0.3 + atr_c * 0.3) / 0.9) as rating 
from analytics_product 
ORDER BY rating DESC, year DESC

I have a relation of each product can have an entry in the rating table.

class Review_product(db.Model):
    __tablename__ = 'review_product'
    id = db.Column(db.Integer, primary_key=True)
    last_year = db.Column(db.SmallInteger) 
    analytics = db.relationship('Review_product', backref='review_product',  lazy='joined', uselist=False)

class Analytic_product(db.Model):
    __tablename__ = 'analytics_product'
    id = db.Column(db.Integer, primary_key=True)
    review_id = db.Column(db.Integer, db.ForeignKey('review_product.id'), nullable=False)
    atr_a = db.Column(db.Integer)
    atr_b = db.Column(db.Integer)
    atr_c = db.Column(db.Integer)

How can i generate the same output as the query above using these models? Please not that i don't have the last_year in the analytics table.

SQL Requests against local SQL database with android simulator

This is kind of a weird question, so I'm open to suggestions.

I'm working on an Android app (using Xamarin, although I don't think that's relevant here other than to tell you that it's in c#). I would like to make some sql queries from the Android app to get and update some information on a sql server. I think I've figured out how to do this properly - unless there is some special case for android, I mainly followed this tutorial: http://ift.tt/1R0aEE0

Assuming that works, my issue has to do with testing it. I'm running the application on the android simulator, and the sql database is running locally. However, it appears that the simulator is not able to talk to the local database (which would make sense) since I'm getting:

System.Data.SqlClient.SqlException: Server does not exist or connection refused. ---> Mono.Data.Tds.Protocol.TdsInternalException: Server does not exist or connection refused. ---> System.Net.Sockets.SocketException: Could not resolve host 'HOST'

So, my question is: is there a way for me to test my android application's sql functionality using a local sql database and the android simulator? If not, what's the simplest way I could test this functionality?

JPA criteria query in a many-to-many relationship using IN operator

I have the following two entities:

Profile, Category

In the Profile entity i have a List of categories that the profile has

In the Category entity i have a List of profiles that the category has

The generated table is called profiles_has_categories and has the following columns:

profile_id, category_id

I want to find all profiles that belong to any of the categories with the following ids 1, 2, 3, 4

I would do that in plain sql with:

SELECT p.* FROM profiles p, profiles_has_categories phc 
WHERE p.id = phc.profile_id 
AND phc.category_id IN (1, 2, 3, 4)

But i can't figure out how to do that using the CriteriaBuilder to construct a query in JPA:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Profile> criteria = cb.createQuery(Profile.class);
Root<Profile> root = criteria.from(Profile.class);

// Profile_.categories is a collection of Category objects
// categories is collection of Long (category ids) that i want to search in
// but is not working
criteria.where(root.get(Profile_.categories).in(categories))

List<Profile> results = em.createQuery(criteria).getResultList();

Laravel 5: Convert raw SQL using WhereIn to Eloquent

Hope you can help me converting this raw sql because the converted one I have is not working.

Raw SQL

$connected = DB::select(DB::raw('SELECT count(*)
    FROM users
    WHERE
        id != '.$id.'
        AND id IN (SELECT from_user FROM connections WHERE to_user = '.$id.' AND status = 3)
        OR id IN (SELECT to_user FROM connections WHERE from_user = '.$id.' AND status = 3)
'));

SQL Converted to Eloquent

$connected  = User::whereIn('id', function($q) use ($id) {
    $q->select('connections.from_user');
    $q->where('connections.to_user', $id);
    $q->where('connections.status',3);
})
->orWhereIn('id', function($q) use ($id) {
    $q->select('connections.to_user');
    $q->where('connections.from_user',   $id);
    $q->where('connections.status',3);
})
->count();

How to get calculations from two rows

I started learning SQL recently and would like to know if it is possible to do the calculations as below.

Basically my table looks like this:

id    Date           Fill_nbr     
 1   01/01/2015       30      
 1   02/05/2015       30      
 1   03/02/2015       30      
 1   07/01/2015       30      
 1   07/26/2015       30      
 2   03/01/2015       30      
 ....

And I'd like to create a table like this:

 id  Date             Fill_nbr     Date_last      Gap    First
 1   01/01/2015       30           01/30/2015      0         1
 1   02/05/2015       30           03/04/2015      5         0
 1   03/02/2015       30           03/31/2015      0         0
 1   07/01/2015       30           07/30/2015      91        1
 1   07/26/2015       30           08/24/2015      0         0
 2   03/01/2015       30           03/30/2015      0         1
 ....

The rule for column 'Date_last' is Date_last = Date + fill_nbr which is easy to get.

The difficult part for me is the 'Gap' part. The rules are: * Gap='Date' - last record of "Date_last'. For example, gap for the second row is calculated as Gap=02/05/2015- 01/30/2015; * Gap=0 for everyone's first record or when the calculated gap<0;

The rule for column 'First': * First=1 for everyone's first record OR when gap>60. * Otherwise, First=0;

Any suggestions would be greatly appreciated! Thanks!

Trying to create a large query, keeps getting truncate

I have a SQL query in SQL server where I am trying to create large query with 'union' on a large number of databases. However, the query keeps getting truncated. According to my research this shouldn't happen if all the varchar are cast to varchar(MAX). I tried doing this, however, it still gets truncated. The final query should be in the @finalQuery varible. Can anyone help with the query below?

DECLARE @name VARCHAR(MAX) -- database name  
DECLARE @path VARCHAR(MAX) -- path for backup files  
DECLARE @fileName VARCHAR(MAX) -- filename for backup  
DECLARE @fileDate VARCHAR(MAX) -- used for file name 
DECLARE @executeQuery VARCHAR(MAX)
DECLARE @finalQuery VARCHAR(MAX)

SET @finalQuery = ''
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master..sysdatabases
WHERE name NOT IN (CAST('master' AS VARCHAR(MAX)),CAST('model' AS VARCHAR(MAX)),CAST('msdb' AS VARCHAR(MAX)),CAST('tempdb' AS VARCHAR(MAX)))  
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN




SET @executeQuery=CAST('SELECT TOP 1000 
      [EmailAddress] as ''Email Address''
      ,[FirstName] as ''First Name''
      ,[LastName] as ''Last Name''
      ,[LastLogin] as ''Last Login'',
        Name as ''User Role''
  FROM '+@name+'.[dbo].[User] c
  INNER JOIN 
  (SELECT * FROM '+@name+'.[dbo].[SecurityRole] as a 
  INNER JOIN '+@name+'.[dbo].[SecurityRoleToUser] as b
  ON (a.ID=b.SecurityRoleID)
  ) d

  ON (c.ID=d.UserID)
  WHERE IsActive=1' AS VARCHAR(MAX))


  --PRINT @executeQuery
 -- PRINT @name
  --PRINT @executeQuery
  SET @finalQuery = CAST(@executeQuery+' UNION ALL ' +@finalQuery AS VARCHAR(MAX))
  --PRINT @executeQUery
    --EXEC (@executeQuery)
FETCH NEXT FROM db_cursor INTO @name

END
CLOSE db_cursor
DEALLOCATE db_cursor
PRINT @finalQuery
--EXEC(@finalQuery)

MySQL group by all columns except one

I'm looking for a (cleaner?) way to do the following:

Let's say I have a table, main, with ~15 columns that looks something like this, with one row per id:

main:
id      start           end             col4    ...     col15
666     2014-01-01      2014-06-30      ...     ...     ...
1234    2015-03-05      2015-05-02      ...     ...     ...
9876    2014-09-01      2015-01-01      ...     ...     ...
...(etc)

Then I have another table, events, which may have 0, 1, or many rows per id:

events:
id      date            code
666     2014-01-20      "code_a"
1234    2015-05-01      "code_b"
666     2014-01-25      "code_c"
666     2014-02-09      "code_z"
... (etc)

and finally I have a table, codes, which has one row per code, giving a description for the code as well as a type (0,1, or 2):

codes:
code            desc            type
"code_a"        "something"     0 
"code_b"        "somethn else"  1
"code_c"        "another thing" 0
"code_d"        "one more"      2
(no code z)

and what I want as a result is main's 15 columns plus three additional columns which contain comma separated lists of event codes which happened between the start and end dates for that id by type (first column is type 0, second type 1, third type 2), so:

id      start           end             ...     col15   type_0          type_1  type_2
666     2014-01-01      2014-06-30      ...     ...     "code_a,code_c"         
1234    2015-03-05      2015-05-02      ...     ...                     "code_b"
...(etc)

my solution is

select m.*
     , group_concat(c0) as type_0
     , group_concat(c1) as type_1
     , group_concat(c2) as type_2
from main m 
     left join events e on m.id = e.id and e.date between m.start and m.end
     left join codes c0 on c0.code = e.code and c0.type = 0
     left join codes c1 on c0.code = e.code and c0.type = 1
     left join codes c2 on c0.code = e.code and c0.type = 2
group by m.id
       , m.start
       , m.end
       , m.col4
       , m.col5
       , m.col6
       , m.col7
       , m.col8
       , m.col9
       , m.col10
       , m.col11
       , m.col12
       , m.col13
       , m.col14
       , m.col15  

But to me that's pretty nasty looking. Is there a more elegant way to do this (especially avoiding the 15 columns listed in the group by)?

SQL Server : Trace won't create file

I'm trying to start using SQL Trace and I am running into "Access Denied" errors when using the following:

exec @rc = sp_trace_create @TraceID output, 0, N'C:\Users\USER$\Desktop\SQLTrace', @maxfilesize, NULL 

if (@rc != 0) goto error

I have tried UNC path and local, I am using an admin account to login to Mgmt Studio, what component am I missing? I have tried saving locally and to a remote client.

MS SQL Read value with apostrophe from table and save it in another table

I am reading a value from table with apostrophe with which I create a dynamic query and than I run a sp to save it in another table, which works fine without apostrophe but throw an error when it contains an apostrophe.

Difference in SQL queries with or without parameters

Can anyone tell me what the difference (besides the obvious) is between these two queries: 1)

declare @coy_oid varchar
declare @field_name varchar

set @coy_oid = '10'
set @field_name = 'ResultReason'

SELECT OID, POSITION, DESCRIPTION, FIELD_NAME 
FROM T_FIELD_TEXT_CHOICES 
WHERE COY_OID = @coy_oid AND FIELD_NAME = @field_name 

2)

declare @coy_oid varchar
declare @field_name varchar

set @coy_oid = '10'
set @field_name = 'ResultReason'

SELECT OID, POSITION, DESCRIPTION, FIELD_NAME 
FROM T_FIELD_TEXT_CHOICES
WHERE COY_OID = @coy_oid AND FIELD_NAME = 'ResultReason'

The first one returns nothing and the second returns the expected results. I am sure it has to do with the FIELD_NAME being a variable, but I don't know why.

Guess I should add this is SQL Server 2008 R2, but maybe it doesn't matter.

INSERT from memory to SQL inefficient

So I have been trying to optimize a situation. Essentially, I have a WEB API service that queries a SQL Server Database and then inserts it into a SQLite database on a client machine.

After running some tests. I found that it took 0.50% of the time to load the data from SQL Server into an array of objects in C# and the insert to all of the other time.

Is my insert incredibly inefficient?

Here is my method:

public void addDataToTable()
{
    using (SQLiteConnection SQLconn = new SQLiteConnection(SQLiteConnectionString))
    {
        SQLconn.Open();
        SQLiteCommand cmd = SQLconn.CreateCommand();
        foreach (Step row in allSteps)
        {
            cmd.CommandText = "INSERT INTO Step (StepID, EWPID, StepNum, Description, MTOQty, ToDateQty, CraftCode, OriginPkg, SF01, SF10, Updated) VALUES " + 
                "(@param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8, @param9, @param10, @param11);";
            cmd.Parameters.Add(new SQLiteParameter("@param1", row.StepID));
            cmd.Parameters.Add(new SQLiteParameter("@param2", row.EWPID));
            cmd.Parameters.Add(new SQLiteParameter("@param3", row.StepNum));
            cmd.Parameters.Add(new SQLiteParameter("@param4", row.Description));
            cmd.Parameters.Add(new SQLiteParameter("@param5", row.MTOQty));
            cmd.Parameters.Add(new SQLiteParameter("@param6", row.ToDateQty));
            cmd.Parameters.Add(new SQLiteParameter("@param7", row.CraftCode));
            cmd.Parameters.Add(new SQLiteParameter("@param8", row.OriginPkg));
            cmd.Parameters.Add(new SQLiteParameter("@param9", row.SF01));
            cmd.Parameters.Add(new SQLiteParameter("@param10", row.SF10));
            cmd.Parameters.Add(new SQLiteParameter("@param11", row.Updated));
            cmd.ExecuteNonQuery();
        }
    }
}

allSteps is an array of objects that coincides with the columns from SQL I'm getting:

public class Step
{
    public int StepID { get; set; }
    public int EWPID { get; set; }
    public int StepNum { get; set; }
    public string Description { get; set; }
    public double MTOQty { get; set; }
    public double ToDateQty { get; set; }
    public string CraftCode { get; set; }
    public string OriginPkg { get; set; }
    public string SF01 { get; set; }
    public string SF10 { get; set; }
    public string Updated { get; set; }
}

Is there a way to dump an entire Select statement into a SQLite table without the looping?

Using "when others then Null;" is 01 recommended best practice?

Help with this please Using "when others then Null;" is 01 recommended best practice?

Does order matter in MySQL for short circuiting of predicates?

Let's say I have to run the SQL query:

SELECT data FROM table WHERE condition1 AND condition2 AND condition3 AND condition4 

Is that any different than

SELECT data FROM table WHERE condition3 AND condition1 AND condition4 AND condition2

?

---

If it's not different:

I know from my own experience that condition1 is less expensive than condition2 is less expensive than condition3 is less expensive than condition4.

If any of the prior conditions are not met, the remaining conditions should never be checked. It wouldn't be immediately obvious to the optimizer, as stored functions are involved. How should I write a query that does this?

SQL - select rows not similar to above in the same column in one table

I've got the following result when I tried to fetch the results from the database with mysql command :

select player_id,full_name,club_name from players

something like that :

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   Petrojet  |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

but I have many club names similar to above

+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   El Ahly   |
+----+------+---------------------+

OR

+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+

and I have tried to use ORDER BY RAND(club_name) and it gave me a result like that

Ex:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Ismaily   |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

The desired output would be:

+----+------+---------------------+
| id |   full_name  |  club_name  |
+----+------+---------------------+
| 1  | Ahmed Sayed  |   El Ahly   |
+----+------+---------------------+
| 2  | Kareem Gaber |   Petrojet  |
+----+------+---------------------+
| 3  | Wael Gamal   |   ENPPI     |
+----+------+---------------------+
| 4  | Mohab Saeed  |   El Ahly   |
+----+------+---------------------+
| 5  | Kamal saber  |   Cocorico  |
+----+------+---------------------+
| 6  | Mohamed mezo |   Ismaily   |
+----+------+---------------------+
| 7  | Mohamed gad  |   Cocorico  |
+----+------+---------------------+
| 8  | moaz maged   |   Smouha    |
+----+------+---------------------+

can mysql do that or should I integrate php with mysql ? Any help would be much appreciated.

how to filter all columns together having not null value in sql server

I have about 20 columns and want to filter all of them together having only 'not null' values. Is there a way to do this in sql since I don't want to mention all column names in my query.

Something like this -

Select * from table_name where columns IS NOT NULL

How to determine if sql table is Temporal?

With SQL Server 2016 supporting Temporal Tables I wonder if there is a way to determine if a table is currently temporal? Something like

select * from sys.objects where object_id('dbo.MyTable', 'u') = parent_object_id and type_desc = "SYSTEM_VERSIONED"

What is Oracle's default FK behaviour ON DELETE?

If I simply stipulate a constraint of the form:

ALTER TABLE childtbl
    ADD (CONSTRAINT myfk
         FOREIGN KEY(mycol)
         REFERENCES parenttbl(mycol))

in an Oracle database, what is the default behaviour if I delete a row in parenttbl?

• CASCADE?
• SET NULL?
• RESTRICT?
• NO ACTION?

SQL find missing language entries in table

I have a table which is missing some entries for a certain language. How can I get a list of all language text in english (lang 1 in table) which is missing the foreign translation counterpart (lang 2)

My table is as follows

PageName | LanguageNo | TranslationName | TranslationText   |
main     |     1      | SomeName        | some english text |
main     |     2      | SomeName        | some foreign text |
main     |     1      | SomeName2       | some english 2    |
other    |     1      | SomeName3       | some english 3    |
other    |     2      | SomeName3       | some foreign 3    |

For example, using the above table data, only the following should be returned:

main     |     1      | SomeName2       | some english 2    |

How can I write a SQL statement to achieve this?

Thanks

How to do conditional sum with PostgreSQL?

I have the following table:

ID  Quantity     date
100    20     1-NOV-15
100    30     1-OCT-15
100    10     1-OCT-15
100    5      1-AUG-15
101    4      1-AUG-15

I want to sum for each ID all the Quantity till the Date associated with the ID meaning I want to get this:

ID  Quantity     Date      sum
100    20     1-NOV-15      65       // sum all ID 100 till 1-NOV-15
100    30     1-OCT-15      45       // sum all ID 100 till 1-OCT-15
100    10     1-OCT-15      45       // sum all ID 100 till 1-OCT-15
100    5      1-AUG-15       5       // sum all ID 100 till 1-AUG-15
101    4      1-AUG-15       4       // sum all ID 101 till 1-AUG-15

I'm having trouble getting this result. This is what I wrote:

Select ID,Date,SUM(Quantity)
From a
Group by ID,Date
order by ID

I can't find out how to tell the SUM to pass on all records which are the same ID and their Date is smaller.

How to partition by a cusomized sum value?

I have a table with the following columns: customer_id, event_date_time

I'd like to figure out how many times a customer triggers an event every 12 hours from the start of an event. In other words, aggregate the time between events for up to 12 hours by customer.

For example, if a customer triggers an event (in order) at noon, 1:30pm, 5pm, 2am, and 3pm, I would want to return the noon, 2am, and 3pm record.

I've written this query:

select
    cust_id,
    event_datetime,
    nvl(24*(event_datetime - lag(event_datetime) over (partition BY cust_id ORDER BY event_datetime)),0) as difference
from
  tbl

I feel like I'm close with this. Is there a way to add something like

 over (partition BY cust_id, sum(difference)<12 ORDER BY event_datetime)

SELECT Statement in CASE

Please don't downgrade this as it is bit complex for me to explain. I'm working on data migration so some of the structures look weird because it was designed by someone like that.

For ex, I have a table Person with PersonID and PersonName as columns. I have duplicates in the table.

I have Details table where I have PersonName stored in a column. This PersonName may or may not exist in the Person table. I need to retrieve PersonID from the matching records otherwise put some hardcode value in PersonID.

I can't write below query because PersonName is duplicated in Person Table, this join doubles the rows if there is a matching record due to join.

SELECT d.Fields, PersonID
FROM Details d
JOIN Person p ON d.PersonName = p.PersonName

The below query works but I don't know how to replace "NULL" with some value I want in place of NULL

SELECT d.Fields, (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName )
FROM Details d

So, there are some PersonNames in the Details table which are not existent in Person table. How do I write CASE WHEN in this case?

I tried below but it didn't work

 SELECT d.Fields, CASE WHEN (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName) = null THEN 123 ELSE (SELECT TOP 1 PersonID FROM Person where PersonName = d.PersonName) END Name
    FROM Details d

This query is still showing the same output as 2nd query. Please advise me on this. Let me know, if I'm unclear anywhere. Thanks

SQL query with having

I've some problems with understanding how having works. It's clear to me when I check the count of something, but is this the only use case for it?

For example I have a tasks table and tasks_parameters table which have got columns: key, value, task_id. Now, I would like to select all tasks which have got two parameters:

key = 'type' && value = 'work'
key = 'priority' && value = 'normal'

I was thinking about that query:

select t.id from tasks t
join tasks_parameters p on (p.task_id=t.id)
group by t.id, p.key, p.value
having p.key = 'type' and p.value = 'work' and
       p.key = 'priority' and p.value = 'normal';

but, ofcourse, it doesn't work, because parameter can't have two diffrent values for the key. Is it even possible to query something like this?

SQL Server LIKE clause returns only some matching rows

I'm trying to select rows from a table where there are some matching characters in the Name field. From within PHP, the where clause looks like this:

        $where = "LTRIM(RTRIM(name)) Like '%".$name."%' ";

        $sql = "SELECT name, email, Department as department, room, Telephone as telephone FROM ".$this->vcsd_tbl." WHERE ".$where." ORDER BY name ASC;";

        $statement = sqlsrv_query($this->vcsd_connection, $sql);
        $result = array();
        $index = 0;

        while ($result[$index] = sqlsrv_fetch_array($statement, SQLSRV_FETCH_ASSOC))
        {
            $index++;
        }
        return $result;

It's returning one row with a match on the name field, but it doesn't return another row that has the exact same match on the name field.

Would anyone happen to know why?

Group by with MIN value in same query while presnting all other columns

I have a view called a with this data:

 ID    tDate        name     task   val
23   2015-06-14
23   2015-06-25
126  2015-06-18
126  2015-06-22
126  2015-06-24

ID is integer and tDate is timestamp.

Basically I want to get for each ID the min value of tDate and present this row. meaning:

ID   tDate       name     task   val
23   2015-06-14
126  2015-06-18

I wrote this query:

select ID, min(tDate)
from a
group by ID
order by ID

This is working BUT it doesn't allow me to present all other columns of a

for example if I do:

select ID, min(tDate), name
from a
group by ID
order by ID

it says that name must be under group by. So I wrote this query:

select ID, MIN(tDate), name, task, val , ....
from a
group by ID, name, task, val , ....
order by ID

And this one doesn't work. it gives false results.

How do I solve it?

why we provide alias names after 'DELETE' in multiple delete

This might be a very stupid questions, but I am keen to know if anyone has any suggestions:

Single Table Delete Correct Query : delete from vehicle_owner where id=3;

Single Table Delete InCorrect Query : delete from vehicle_owner v where v.id=3

Mulitple Table Delete : delete v,s from vehicle v , category s where v.id=3 and v.id=s.id;

Qs 1: I was wondering that why 1st is right and why 2nd is incorrect. Basically I am looking for logical answers which explains why providing an alias in delete query is incorrect.

Qs 2: why we keep two alias names after 'DELETE' keyword for multiple delete. Anyways we are providing complete details in join condition. So why it is designed in such a way.

Eliminate duplicate rows containing blanks

I have a condition where I have one header and multiple lines for an item.
I am attempting to add the values from the line join to my header query.
Valid values for this PGM_CD field are S, L, V, Blank etc.

If a line has a value on one line and blank on the others, I need to report the actual value at the header level and not blank values.
ie - line 1 is blank, line 2 is V, I want to report V.
Since blank is a valid value on some, I can't eliminate blanks in my subquery completely.

How do I interrogate all the lines to find that they are either all blank, where I would report blank at the header level or that there is a value on one of the lines in which case I would report that value at the header?

Here's a subset of my query

SELECT 
hdr.field1
hdr.field2
hdr.field3
au.PGM_CD   (this field can have the blanks, v, l, etc. values)
 FROM HEADER hdr
 JOIN (SELECT DISTINCT l.ID, PGM_CD
  FROM LINE l 
  LEFT JOIN AUTH a
  ON l.ID = a.ID) AS au

Can I use raw sql insert or update in Sails?

According to the documentation, http://ift.tt/1QVYk7K, I'm able to use query() method to use native sql 'queries', but I'm confused whether insert or update is considered as 'queries' because some people use the term 'queries' to mean insert or update in sql.

Validating Information....error with query...No connection could be made because the target machine actively refused it

I have created php connect with php database. I checked locally its working well. I have moved server Its showing error. changed the database config details. It is showing, but this code connect xampp locally that time working well. I get this error:

Validating Information....error with query: SELECT ID,fname,lname,email,userlevel,user_group,orgID FROM students WHERE username='test' AND password='123456' No connection could be made because the target machine actively refused it.

How to INNER JOIN a variable number of tables

I have a table-valued function that returns the members of an AD group:

SELECT GroupName, MemberName FROM dbo.ShowMembers('AD Group Name')

I would like to be able to specify a few AD Group Names and perform an INNER JOIN on each of them to find the common members of all specified AD Group Names.

This works but if I have a number (2-6) of different AD groups to join, it would be easier to add them to a temporary table or variable:

SELECT * FROM dbo.ShowMembers ('Group1') m1, dbo.ShowMembers('Group2') m2
WHERE m1.MemberName = m2.MemberName

Get Thread information from Single Database

I am trying to learn how to create a simple forum and i stuck in here;

My Database is something like this; (post table)

ThreadID |  PostID  | Author |  Title   |  Content |    Date    |    Time
--------------------------------------------------------------
   1     |     1    |  Jack  | Thread 1 |    ...   | 14/12/2015 |   20:21
   1     |     2    |  Arn   |          |    ...   | 15/12/2015 |   19:28
   1     |     3    |  Hank  |          |    ...   | 15/12/2015 |   20:24
   2     |     1    |  Tom   | Thread 2 |    ...   | 15/12/2015 |   22:41
   2     |     2    |  Frank |          |    ...   | 16/12/2015 |   13:06

Post table contains both replies and threads And i would like to get some information from database on category segment

and this is what i want;

Title      |   Author   |   Replies   | Views |               Last Poster
-------------------------------------------------------------------------
Thread 1   |    Jack    |      2      |   -   |   Hank - 20:24 15/12/2015
Thread 2   |    Tom     |      1      |   -   |  Frank - 20:24 15/12/2015

and finally my code is;

$get_threads = "SELECT threadid, title, author, postid, date, time FROM post WHERE categoryid = '" . $category . "'";   
$threads = mysqli_query($conn, $get_threads); if (mysqli_num_rows($threads) > 0) {while($row = mysqli_fetch_assoc($threads)) {
$get_reply = "SELECT (MAX(postid)-1) rn, (max(postid)) lp FROM post WHERE threadid = '".$row["threadid"]."'";
$reply = mysqli_query($conn, $get_reply); if (mysqli_num_rows($reply) > 0) {while($rod = mysqli_fetch_assoc($reply)) {
$get_lp = "SELECT op.author, op.date, op.time FROM post op INNER JOIN (SELECT author, date, time FROM post WHERE POSTID = '".$rod["lp"]."' GROUP BY threadid) lp ON op.author = lp.author AND op.date = lp.date AND op.time = lp.time WHERE threadid = '".$row['threadid']."'";
$lp = mysqli_query($conn, $get_lp); if (mysqli_num_rows($lp) > 0) {while($roc = mysqli_fetch_assoc($lp)) {

echo "<div class='thread'><div class='threadbox'>";
echo "<a href='forum.php?post=".$row["threadid"]."'class='ttitle'>".$row["title"]."</a>";
echo "<div class='tauthor'>".$row["author"]."</div>";
echo "<div class='treplies'>".$rod["rn"]."</div>";
echo "<div class='tviews'>".$row["date"]."</div>";
echo "<div class='tlastposter'>".$roc['author']." - ".$roc["time"]." ".$roc["date"]."</div>";
echo "</div></div><div class='threaddiv'></div>";

}}}}}} else {echo "<center>Content Not Found</center>";}

Well it may be the worst code ever written but its my first, i apologize for that anyway its duplicating some results i couldnt figure out.

1. Please help me about how i can handle it?
2. If you improove and shorten this code i would be glad ^^

Thanks in Advance

UniVerse - SQL LIST: View List of All Database Tables

I am trying to obtain a list of all the DB Tables that will give me visibility on what tables I may need to JOIN for running SQL scripts.

For example, in TCL when I run "LIST.DICT" it returns "Name of File:" for input. I then enter "PRODUCT" and it returns a list of all available fields.

However, Where can I get a list of all my available Tables or list of my options that I can enter after "Name of File:"?

Here is what I am trying to achieve. In the screen shot below, I would like to run a SQL script that gives me the latest Log File Activity, Date - Time - Description. I would like the script to return '8/13/14 08:40am BR: 3;BuyPkg'

Thank you in advance for your help.

The multi-part identifier could not be bound MSSQL error

I searched SO and Google for awhile but couldn't seem to find any that related to this error occurring in the set part of the code and if it did occur in that part, the solution didn't help me.

I am trying to use a user inputted weight and subtract it by the total weight. This equals the Shrink

SET @Shrink  = @InputWeight -  [ICPL].[OriginalQuantity_Stk];

I also tried it this way and got the same error.

SET @Shrink  = @InputWeight - Sum([ICPL].[OriginalQuantity_Stk]);

The table is called IC_ProductLots and the alias is ICPL

The error I get is:

The multi-part identifier "ICPL.OriginalQuantity_Stk" could not be bound.

Here are my joins if that helps at all:

 FROM (((( IC_Products [PC] 
    INNER JOIN  DC_Transactions [DCT] 
     ON [PC].ProductKey = [DCT].ProductKey)
    INNER JOIN  AR_Customers 
     ON [DCT].CustomerKey = AR_Customers.CustomerKey)
    INNER JOIN  IC_ProductLots [ICPL] 
     ON [DCT].LotKey = [ICPL].LotKey)
    LEFT OUTER JOIN  IC_ProductCosts [ICP] 
     ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)

Let me know if seeing any more code would help.

How to auto fill in text boxes with data from database?

I want to fill all the text boxes with data that corresponds to the name that is chosed on the dropdown box. I have searched for codes for it to work and unluckily none of it works.

PHP

<?php
    include("webconfig.php");
    session_start();    
?>

HTML

<!doctype html>
<html>
<head>
<meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <link rel="shortcut icon" href="img/logo_trans.png">
    <link rel="stylesheet" href="css/iconFont.css">
    <link rel="stylesheet" href="css/metro-bootstrap.css">
    <link rel="stylesheet" href="css/metro-bootstrap-responsive.css">

    <script src="js/jquery/jquery.min.js"></script>
    <script src="js/jquery/jquery.widget.min.js"></script>            
    <script src="js/load-metro.js"></script>

    <!-- Local JavaScript -->
    <script src="js/docs.js"></script>
    <script src="js/github.info.js"></script>
    <script src="js/Chart.js"></script>
    <script src="js/metro-dropdown.js"></script>
<title>AS_SIGNS</title>
</head>

<body class="metro">
        <nav class="horizontal-menu" style="background:#037E04;">
            <ul>
             <center><li><a href="index.php"><h1><font color="#FFFFFF">AS Signs</font></h1></a></li></center>
            </ul>
       </nav>

    <div style="width:70%; height:60%; position:absolute; left:0;">
        <div  style="width:100%; height:100%;overflow:scroll; overflow-x:hidden; overflow-y:auto;">
        <div style="width:50%; float:left;">
            <h2>New Service Call</h2>
            <form method="post" name="form">
                    <table  class="table bordered" width="100%">
                    <tr>    
                        <td>Customer ID</td>
                        <td><input type="text" name="custid" /></td>
                    </tr>

                    <tr>    
                        <td>Customer Name</td>
                        <td>
                        <select name="CUSTOMER_NAME" style="width:205px" required>
                                <option value="default"></option>
                                 <?php
                                    $sql1 = "SELECT * FROM CUSTOMER_MASTER";
                                    $sth = ibase_query($conn, $sql1) or die(ibase_errmsg());

                                    $num = '';

                                    while($row = ibase_fetch_assoc($sth))
                                    {


                                    $name = $row["ACCOUNT_NAME"];

                                    echo "<option value=\"$name\">$name</option>";

                                    }
                                ?>


                        </select>
                        </td>   
                        </tr>
                    <tr>
                        <td>Address</td>
                        <td><input type="text" name="ADDRESS" value="<?php echo "$add"?>"></td>
                    </tr>
                    </table>

        </div>
        <div style="width:50%; float:right;">
             <h2 style="visibility:hidden;">cqcqcqcqc</h2>
                    <table  class="table bordered" width="100%">

                    <tr>
                        <td>Contact Person</td>
                        <td><input type="text" name="CONTACT_PERSON" value="<?php echo "$contperson"?>"></td>
                    </tr>
                    <tr>
                        <td>Contact Number</td>
                        <td><input type="text" name="CONTACT_NUMBER" value= "<?php echo "$contnum"?>"> </td>
                    </tr>
                    </table>    
                     <input type="submit" name="btnSave" value="SAVE" />
                     <input type="button" name="load" value="LOAD" />

                    <?php
                    if(isset($_POST['load']))
        {
                    $sql = "SELECT * FROM CUSTOMER_MASTER WHERE CUSTOMER_NAME = '$name'";
                    $result = ibase_query($query, $conn);
                    $details = ibase_fetch_assoc($result);                                  

                    $sth = ibase_query($conn, $sql) or die(ibase_errmsg());

                    $add = $details["ADDRESS"];
                    $name = $_POST["ACCOUNT_NAME"];
                    $servicenum = $details["SERVICE_CALL_NUMBER"];
                    $contperson = $details["CONTACT_PERSON"];
                    $contnum = $details["CONTACT_NUMBER"];


                    }

                    ?>

                    <?php
                    $sql = "SELECT * FROM SERVICE_REPORTS";


                    $sth = ibase_query($conn, $sql) or die(ibase_errmsg());
                    $add = $_POST["ADDRESS"];
                    $name = $_POST["CUSTOMER_NAME"];
                    $servicenum = $_POST["SERVICE_CALL_NUMBER"];
                    $contperson = $_POST["CONTACT_PERSON"];
                    $contnum = $_POST["CONTACT_NUMBER"];



        if(isset($_POST['btnSave']))
        {

            ibase_query($conn, "INSERT INTO SERVICE_REPORTS (CUSTOMER_NAME, ADDRESS, CONTACT_PERSON, CONTACT_NUMBER) VALUES('$name', '$add', '$contperson', '$contnum')") or die (ibase_errmsg());

            echo "Record Saved";
        }


    ?>
          </form>   
        </div>
        </div>
    </div>

  <div class="span5" style="position: absolute;right:0; top:100px;">
        <nav class="sidebar dark">
            <ul>
                <li class="active"><a href="#"><i class="icon-home"></i>Home</a></li>
                <li class="stick bg-red">
                    <a class="dropdown-toggle" href="#"><i class=""></i>Service Call</a>
                        <ul class="dropdown-menu" data-role="dropdown" data-effect="slide">
                            <li><a href="servicecallnew.php">New Service</a></li>
                            <li><a href="#">Service Call List</a></li>
                        </ul>
                </li>                            
                <li class="stick bg-red">
                    <a class="dropdown-toggle" href="#"><i class="icon-sale"></i>File Maintenance</a>
                    <ul class="dropdown-menu" data-role="dropdown" data-effect="slide">
                        <li><a href="items.php?itd=&action">Items</a></li>
                        <li><a href="categories.php">Categories</a></li>
                        <li><a href="contractors.php">Contractors</a></li>
                        <li><a href="suppliers.php">Suppliers</a></li>
                    </ul>
                </li>

                  <li class="stick bg-red">
                    <a class="dropdown-toggle" href="#"><i class="icon-box-add"></i>Production</a>
                    <ul class="dropdown-menu" data-role="dropdown">




                                <li><a href="estimatelist.php">Estimate / Quotation</a></li>
                                <li><a href="bomlist.php">Bill of Materials</a></li>
                                <li><a href="bomposting.php">BOM Posting</a></li>
                                <li><a href="issuancelist.php">Issuance</a></li>
                                <li><a href="adjplist.php">Adjustment</a></li>


                      </ul>
                  </li>


                  <li class="stick bg-red">
                    <a class="dropdown-toggle" href="#"><i class="icon-box-add"></i>Inventory</a>
                    <ul class="dropdown-menu" data-role="dropdown"> 
                                <li><a href="receiving.php">Receiving</a></li>

                      </ul>
                  </li>

                  <li class="stick bg-red">
                    <a class="dropdown-toggle" href="#"><i class="icon-printer"></i>Reports</a>
                    <ul class="dropdown-menu" data-role="dropdown">

                             <!--   <li><a onClick="<?php echo "window.open('report_prod.php?type=estimate','mywindow3')"; ?>">Estimate / Quotation</a></li>
                                <li><a onClick="<?php echo "window.open('report_prod.php?type=bom','mywindow3')"; ?>">Bill of Materials</a></li>
                                <li><a onClick="<?php echo "window.open('report_prod.php?type=issuance','mywindow3')"; ?>">Issuance</a></li>
                                <li><a onClick="<?php echo "window.open('report_prod.php?type=adj','mywindow3')"; ?>">Adjustment</a></li>

                                <li><a onClick="<?php echo "window.open('rptinventory.php?','mywindow8','menubar=0,location=1,status=1,scrollbars=1,width=1000,height=500,left=0,top=0,screenX=50,screenY=100')"; ?>">Inventory</a></li>-->

                                    <li><a href="signspec.php">Sign Spec</a></li>
                                    <li><a href="warehouse.php">Warehouse</a></li>


                      </ul>
                  </li>
                                 <li class="stick bg-red"><a href="#"><i class="icon-cog"></i>Settings</a></li>
                <li class="stick bg-red"><a href="logout.php"><i class="icon-switch"></i>Logout</a></li>
            </ul>
        </nav>
    </div>
</body>
</html>

Configuration of EasyPhp wamp

I am using EasyPhp (easyphp.org)

When I list a directory I can only see the filename with a limited number of characters.

• ThisIsAnExample.php

• AndThisIsALongerExampl..>

I managed to expand the with of the table containing the FileName column but the long names are stil croped.

Is there a configuration to allow more characters in the FileName?

Invalid shipping method error message edit in wordpress

Currently I'm using Maya shop WordPress theme. I have done a validation for pincode. If we gave the wrong password means it showing the error message like "Invalid shipping Method" instead of this error message I want my own error message.

How can I achieve this?

Warning (2): session_regenerate_id(): Session object destruction failed

I have setup user login in cakephp with Auth. In core.php I have set sessions to be handled like below so that on every page load the session is renewed

Configure::write('Session', array(
    'defaults' => 'cake',
    'timeout' => 180,
    'cookieTimeout' => 1440,
    'checkAgent' => false,
    'autoRegenerate' => true,
    'requestCountdown' => 1
));

Still sometimes I get this warning:

Warning (2): session_regenerate_id(): Session object destruction failed

And after the next click the user is logged out. Any ideas as to why?

Multi-Color Gradient Fade for Text without CSS?

I'd like help getting this PHP function to work with a third color option.

Note: No CSS. No Javascript. Just PHP and HTML.

<?php

function Gradient($HexFrom, $HexTo, $ColorSteps)
{
        $FromRGB['r'] = hexdec(substr($HexFrom, 0, 2));
        $FromRGB['g'] = hexdec(substr($HexFrom, 2, 2));
        $FromRGB['b'] = hexdec(substr($HexFrom, 4, 2));

        $ToRGB['r'] = hexdec(substr($HexTo, 0, 2));
        $ToRGB['g'] = hexdec(substr($HexTo, 2, 2));
        $ToRGB['b'] = hexdec(substr($HexTo, 4, 2));

        $StepRGB['r'] = ($FromRGB['r'] - $ToRGB['r']) / ($ColorSteps - 1);
        $StepRGB['g'] = ($FromRGB['g'] - $ToRGB['g']) / ($ColorSteps - 1);
        $StepRGB['b'] = ($FromRGB['b'] - $ToRGB['b']) / ($ColorSteps - 1);

        $GradientColors = array();

        for($i = 0; $i <= $ColorSteps; $i++)
        {
                $RGB['r'] = floor($FromRGB['r'] - ($StepRGB['r'] * $i));
                $RGB['g'] = floor($FromRGB['g'] - ($StepRGB['g'] * $i));
                $RGB['b'] = floor($FromRGB['b'] - ($StepRGB['b'] * $i));

                $HexRGB['r'] = sprintf('%02x', ($RGB['r']));
                $HexRGB['g'] = sprintf('%02x', ($RGB['g']));
                $HexRGB['b'] = sprintf('%02x', ($RGB['b']));

                $GradientColors[] = implode(NULL, $HexRGB);
        }
        return $GradientColors;
}

$text = "blah testing blah testing blah";
$length = strlen($text);
$Gradients = Gradient("00FF00", "0000FF", $length);

for ($i=0; $i<$length; $i++) {
    echo '<span style="color: #' . $Gradients[$i] . ';">' . $text[$i] . '</span>';
}


?>

What must I do to get a 3rd option? Such as:

Gradient($HexOne, $HexTwo, $HexThree, $ColorSteps)
                         //^^^^^^^^^

Creating MS File in php using data of MYSQL

<?php
header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=document_name.doc");

echo "<html>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
echo "<body>";
echo "<b>My first document</b>";
echo "</body>";
echo "</html>";
?>

This is a code to create MS file with PHP

What I want to is some thing like this:

<?php
header("Content-type: application/vnd.ms-word");
header("Content-Disposition: attachment;Filename=document_name.doc");

echo "<html>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=Windows-1252\">";
echo "<body>";
echo "<b>".$Variable(data from the table)."</b>";
echo "</body>";
echo "</html>";
?>

Is it possible to create an ms file, with the records from the table?

Bootstrap MODAL REGISTRATION not working

I have this code

  <form id="form" role="form" method="post" action="" autocomplete="off" enctype="multipart/form-data">

<!-- Modal -->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  <div class="modal-dialog">
    <div class="modal-content">
      <div class="modal-header">
        <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
        <h4 class="modal-title" id="myModalLabel">Modal title</h4>
      </div>
      <div class="modal-body">




                <?php
                // Verificar erros
                if(isset($error)){
                    foreach($error as $error){
                        echo '<p class="register-errors">'.$error.'</p>';
                    }
                }

                // Mensagem de sucesso
                if(isset($_GET['action']) && $_GET['action'] == 'joined'){
                    echo "<h2 class='register-sucess'>Registration successful, please check your email to activate your account.</h2> ";
                }
                ?>


    <input type="text" name="username" id="username" class="register-username" placeholder="Username" value="<?php if(isset($error)){ echo $_POST['username']; } ?>" tabindex="1" maxlength="12">


    <br>
    <img id="uploadPreview1" src="../cdn/css/images/no_image.jpg" width="130" height="130"/>

    <input id="uploadImage1" type="file" name="img[]" onchange="PreviewImage(1);" />
           <label for="uploadImage1" >Add profile picture</label>


    <br>



    <input type="email" name="email" id="email" class="register-email" placeholder="Email Address" value="<?php if(isset($error)){ echo $_POST['email']; } ?>" tabindex="2">

    <br>

    <input type="password" name="password" id="password" class="register-password" placeholder="Password" tabindex="3">
    <input type="password" name="passwordConfirm" id="passwordConfirm" class="register-confirmpassword" placeholder="Confirm Password" tabindex="4">

    <br>


    <br>







<?  



if(isset($_POST['submit'])){


        if(strlen($_POST['username']) < 3){
            $error[] = 'Username is too short.';
        } else {
            $stmt = $db->prepare('SELECT username FROM members WHERE username = :username');
            $stmt->execute(array(':username' => $_POST['username']));
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            if(!empty($row['username'])){
                $error[] = 'Username provided is already in use.';
            }
        }

        if(strlen($_POST['password']) < 3){
            $error[] = 'Password is too short.';
        }

        if(strlen($_POST['passwordConfirm']) < 3){
            $error[] = 'Confirm password is too short.';
        }

        if($_POST['password'] != $_POST['passwordConfirm']){
            $error[] = 'Passwords do not match.';
        }

        if(!filter_var($_POST['email'], FILTER_VALIDATE_EMAIL)){
            $error[] = 'Please enter a valid email address';
        } else {
            $stmt = $db->prepare('SELECT email FROM members WHERE email = :email');
            $stmt->execute(array(':email' => $_POST['email']));
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            if(!empty($row['email'])){
                $error[] = 'Email provided is already in use.';
            }

        }


        if(!isset($error)){

            // Proteger a password
            $hashedpassword = $user->password_hash($_POST['password'], PASSWORD_BCRYPT);

            // Criar código de activação
            $activasion = md5(uniqid(rand(),true));

            try {

        $file       = $_FILES['img'];
        $numFile    = count(array_filter($file['name']));

        //PASTA
        $folder     = '../cdn/uploads/avatars/';

        //REQUISITOS
        $permite    = array('image/jpeg', 'image/png');
        $maxSize    = 1024 * 1024 * 1;

        //MENSAGENS
        $msg        = array();
        $errorMsg   = array(
        1 => 'O arquivo no upload é maior do que o limite definido em upload_max_filesize no php.ini.',
        2 => 'O arquivo ultrapassa o limite de tamanho em MAX_FILE_SIZE que foi especificado no formulário HTML',
        3 => 'o upload do arquivo foi feito parcialmente',
        4 => 'Não foi feito o upload do arquivo'
            );

    if($numFile <= 0){
        echo '<script>alert(asd);</script>';
    }
    else{
        for($i = 0; $i < $numFile; $i++){
        $name   = $file['name'][$i];
        $type   = $file['type'][$i];
        $size   = $file['size'][$i];
        $error  = $file['error'][$i];
        $tmp    = $file['tmp_name'][$i];

        $extensao = @end(explode('.', $name));
        $avatar = rand().".$extensao";

        if($error != 0)
            $msg[] = "<b>$name :</b> ".$errorMsg[$error];
        else if(!in_array($type, $permite))
            $msg[] = "<b>$name :</b> Erro imagem não suportada!";
        else if($size > $maxSize)
            $msg[] = "<b>$name :</b> Erro imagem ultrapassa o limite de 1MB";
        else{

        if(move_uploaded_file($tmp, $folder.'/'.$avatar)){


                $stmt = $db->prepare('INSERT INTO members (username,password,email,active,avatar) VALUES (:username, :password, :email, :active, :avatar)');
                $stmt->execute(array(
                    ':username' => $_POST['username'],
                    ':password' => $hashedpassword,
                    ':email' => $_POST['email'],
                    ':active' => $activasion,
                    ':avatar' => $avatar
                ));


     }else{
     $msg[] = "<b>$name :</b> Desculpe! Ocorreu um erro...";

       }
      }
     }
     }

                // Inserir dados na base de dados

                $id = $db->lastInsertId('memberID');

                // Estrutura do email
                $to = $_POST['email'];
                $subject = " Registration Confirmation";
                $body = "Thank you for registering at site.\n\n To activate your account, please click on this link:\n\n ".DIR."activate.php?x=$id&y=$activasion\n\n\n Regards,\n Administration \n\n";
                $additionalheaders = "From: <".SITEEMAIL.">\r\n";
                $additionalheaders .= "Reply-To: ".SITEEMAIL."";
                mail($to, $subject, $body, $additionalheaders);

                header('Location: register.php?action=joined');
                exit;

            } catch(PDOException $e) {
                $error[] = $e->getMessage();
            }

        }

    }


?>

      </div>
      <div class="modal-footer">
        <button type="button" class="btn btn-default" data-dismiss="modal">Close</button>
        <input type="submit" name="submit" value="Register" class="btn btn-primary" tabindex="5">
        </form>
      </div>
    </div>
  </div>
</div>

What happens is this, when I give Submit it does not add anything in the database, only makes a refresh on the page. And also like that mistakes appear in Ajax / jQuery without having to refresh the page after submit. Both email errors, user etc .. As the avatar so there mistakes but do not appear: ss

In short, I wanted help to FIX the part of not going anything to the database and show all errors including Avatar. And all of them in ajax without having to refresh the page ..

Please, anyone help me, it's really urgent :ss

Why is my HTML so poorly formatted when rendered with Smarty?

Why is my HTML so poorly formatted when viewing the source code?

Working with:

1. WAMPSERVER (64 BITS & PHP 5.5) 2.5

2. Slim Framework v2

3. RedBeanPHP 4.2

4. Smarty 3.1.21

---

index.php:

<?php

// load required files
require 'class/Slim/Slim.php';
require 'class/RedBean/rb.php';

// register slim auto-loader
\Slim\Slim::registerAutoloader();

// set up database connection
R::setup('mysql:host=localhost;dbname=slimcms','root','');
R::freeze(true);


// initialize app
$app = new \Slim\Slim(array(
    'mode' => 'development'
    ,'debug' => true
    ,'view' => new \Slim\Views\Smarty()
    ,'templates.path' => './templates'
));


$view = $app->view();

$view->parserDirectory = dirname(__FILE__) . '/class/Smarty/';
$view->parserCompileDirectory = dirname(__FILE__) . '/compiled';
$view->parserCacheDirectory = dirname(__FILE__) . '/cache';



// handle GET request for index
$app->get('/', function() use ($app){
    $books = R::findAll('book');
//print_r($books);
    $app->render('home.tpl',array('books'=>$books));

});


$app->run();

---

templates/home.tpl:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>Home</title>
</head>
<body>    
    <div id="content">
        {foreach name=aussen item=book from=$books}
            {foreach key=key item=value from=$book}
                {if $key == 'id' }
                    <a href="{$key}/{$value}">{$key}</a>
                {else}{$key}{/if}
            {/foreach}
            <hr />
        {/foreach}
    </div>    
</body>
</html>

---

when i view the sourcecode via chrome:

    <!DOCTYPE html>
<html lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title>Home</title>
</head>
<body>
    <div id="content">
                                                        <a href="id/1">id</a>
                                            rating                            price                            title                        <hr />
                                                        <a href="id/2">id</a>
                                            rating                            price                            title                        <hr />
            </div>
</body>
</html>

I would have expected:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
    <title>Home</title>
</head>
<body>
    <div id="content">
        <a href="id/1">id</a> rating price title
        <hr>
        <a href="id/2">id</a> rating price title
        <hr>
    </div>
</body>
</html>