I have column view_to_date in a MySQL table where is date in format '04.05.2015'. I want to select items where view_to_date < current time. Is there some way to do it?
mardi 5 mai 2015
Mysql - how to compare date in string with unix time in query
how to add data into list view from datatable
I have one confusion with SQL Query.
I have three tables, student, books, book_taken_by_student. In the last table we have all the details about books taken by students.
In list view I bind the student name as row vies and different books name as column vies from student and books table. The list view look like
| Book1 | Book2 | Book 3 | Book 4|
A |
B |
C |
D |
E |
F |
Now with help of book_taken_by_student table I want to bind Yes or No in front of student name related with book name taken by them
How I can solve this question.
Finally I want to show my list view like this
| Book1 | Book2 | Book 3 | Book 4|
A | Yes | No | Yes | Yes
B | No | Yes | Yes | No
C | No | Yes | Yes | No
D | Yes | No | Yes | Yes
E | Yes | No | Yes | Yes
F | No | Yes | Yes | Yes
Trigger to cancel transaction
I have to enforce a rule based on a relatively complex query involving four tables. If any of the tables updates/inserts/deletes, I need to run the same query to check if the operation should be denied (by throwing an exception).
I think there must be four separate triggers because it seems that CREATE TRIGGER only accepts a single "on" clause; but I don't want to repeat the query, rather keeping it in a separate stored procedure.
I wonder if there is a way for the verification query to be given a representation of the database state as it would be after the event firing the trigger; and have the query be able to cancel that transaction if needed, rolling back to the state before the trigger was fired. This isn't what "before/for each row" does, I think; because that uses :new and :old - if I were to use the new row, I would have to rewrite the query four times substituting new in place of each respective table.
Adapted answer:
Even if I try my best to be horrible and set autocommit to be on, the 'after statement' trigger does the right thing; i.e. no rows are selected at the bottom of this block.
create or replace trigger test_after_tr
after insert or update or delete on footable
begin
raise_application_error(-20000, 'violated');
end;
/
set autocommit on;
begin
execute immediate 'set autocommit on';
insert into footable(name) values('fail');
exception when others then null;
end;
/
select * from footable where name = 'fail';
Order in for xml T-sql
select a.Hall, a.Title,
STUFF((SELECT ', ' + '[' + CONVERT(varchar(2),DATEPART(Hour, b.StartFilm))
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.StartFilm))
+ ' ' + CONVERT(varchar(2),DATEPART(Hour, b.EndTime))
+ ':' + CONVERT(varchar(2),DATEPART(Minute, b.EndTime))
+ ']'
FROM (select c.Name as Hall, b.Title,
Convert(time,a.StartFilmTime) as StartFilm,
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a
left join Film b on a.FilmId = b.Id
left join Room c on a.RoomId = c.Id
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) = '05-06-2015'
) b
Where a.Hall = b.Hall and a.Title = b.Title
FOR XML PATH('')),1,1,'') As ShowTime
from (select c.Name as Hall, b.Title,
Convert(time,a.StartFilmTime) as StartFilm,
Convert(time,a.EndFilmTime) as EndTime
from FilmSchedule a
left join Film b on a.FilmId = b.Id
left join Room c on a.RoomId = c.Id
where a.ApproveStatus = 1 and a.Status = 1 and CONVERT(date, a.StartFilmTime) = '05-06-2015'
Order by a.StartFilmTime
) a
group by a.Hall, a.Title
i get the error: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Help me! pls
which table structure would be better?
I cant decide which way is better to create a key/value Tag model in Django,
class Tag(models.Model):
tag = models.CharField(max_length=35, unique=True)
description = models.CharField(max_length=250, null=True)
is_key = models.BooleanField(default=False)
parent = models.ForeignKey("self", blank=True, related_name="key")
or
class TagKey(models.Model):
key = models.CharField(max_length=35, unique=True)
description = models.CharField(max_length=250, null=True)
class TagValue(models.Model):
value = models.CharField(max_length=35, unique=True)
description = models.CharField(max_length=250, null=True)
port = models.PositiveIntegerField(default=0)
key = models.ForeignKey(TagKey)
All I intent to do is create a key:value based tag model which I can use to tag my applications.
Explanation: What I am doing in Tag class is that I am giving a self relation to itself when I will be adding a value tag, but if I am adding a key type Tag then I wont be populating the parent field.
P.S I have to use my own tag model so please don't suggest third party Django tag app
Any way to combine these 3 SQL Queries Together?
I have 3 seperate queries at the moment that I'm trying to combine together so it is more efficient.
The reason i'm putting them together is so that I can sort all of the results by submitdate, as of right now they are sorted by submitdate but are seperated by each query.
Would appreciate any help on the matter, thanks a billion guys.
First is
Query that gets all posts that I have commented on that have new comments
SELECT DISTINCT p.*,c.submitdate as MostRecentSubmitDate
FROM posts p
INNER JOIN comments c
ON c.postid = p.id
WHERE c.submitdate > (
SELECT MAX(c2.submitdate)
FROM comments c2
WHERE c2.postid = c.postid
AND c2.deviceID = "$DeviceID"
)
Second is
Query that gets most recent replies on my posts.
SELECT p.PostTitle,p.id AS PostID,c1.id AS CommentID, c1.comment, q.LatestCommentDate, c1.deviceID
FROM (SELECT c.postid, MAX(c.SubmitDate) AS LatestCommentDate
FROM comments c GROUP BY c.postid) q
INNER JOIN posts p ON q.postid = p.id and "$DeviceID" = p.deviceiD
INNER JOIN comments c1 ON q.LatestCommentDate = c1.submitDate
Thirdly is
Query that gets the amount of votes on each of my posts
SELECT * FROM posts
WHERE DEVICEID = "$DeviceID"
AND PostVotes > 0
ORDER BY SUBMITDATE
SQL Query Error - check the manual that corresponds to your MySQL server version
SQL - It's been a while.
I need to figure out why the following SQL is failing:
$query = "INSERT INTO users ('c_id', 'c_email', 'c_fname', 'csname', 'c_mobile', 'c_add_1', 'c_add_2', 'c_city', 'c_county', 'c_postcode', 'c_comments') VALUES ('null','josh','hh', 'hh', 'hhhh', 'hh', 'hhh', 'hhh', 'hhhh', 'hh', 'hhh')";
I have tried it without the c_id being null.
I am getting the following message:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''c_id', 'c_email', 'c_fname', 'csname', 'c_mobile', 'c_add_1', 'c_add_2', 'c_cit' at line 1
Here is my code where the query is executed:
if (mysqli_query($conn,$query))
{
echo "k";
}
else
{
echo mysqli_error($conn);
}
Why does MySQL permit non-exact matches in SELECT queries?
Here's the story. I'm testing doing some security testing (using zaproxy) of a Laravel (PHP framework) application running with a MySQL database as the primary store for data.
Zaproxy is reporting a possible SQL injection for a POST request URL with the following payload:
id[]=3-2&enabled[]=on
Basically, it's an AJAX request to turn on/turn off a particular feature in a list. Zaproxy is fuzzing the request: where the id value is 3-2, there should be an integer - the id of the item to update.
The problem is that this request is working. It should fail, but the code is actually updating the item where id = 3.
I'm doing things the way I'm supposed to: the model is retrieved using Eloquent's Model::find($id) method, passing in the id value from the request (which, after a bit of investigation, was determined to be the string "3-2"). AFAIK, the Eloquent library should be executing the query by binding the ID value to a parameter.
I tried executing the query using Laravel's DB class with the following code:
$result = DB::select("SELECT * FROM table WHERE id=?;", array("3-2"));
and got the row for id = 3.
Then I tried executing the following query against my MySQL database:
SELECT * FROM table WHERE id='3-2';
and it did retrieve the row where id = 3. I also tried it with another value: "3abc". It looks like any value prefixed with a number will retrieve a row.
So ultimately, this appears to be a problem with MySQL. As far as I'm concerned, if I ask for a row where id = '3-2' and there is no row with that exact ID value, then I want it to return an empty set of results.
I have two questions:
-
Is there a way to change this behaviour? It appears to be at the level of the database server, so is there anything in the database server configuration to prevent this kind of thing?
-
This looks like a serious security issue to me. Zaproxy is able to inject some arbitrary value and make changes to my database. Admittedly, this is a fairly minor issue for my application, and the (probably) only values that would work will be values prefixed with a number, but still...
SQL script won't auto commit
Just want to know if the following is the correct way to write SQL script that uses BEGIN TRANSACTION? (something that works like DML)
BEGIN TRY
BEGIN TRANSACTION
/* SQL statements here */
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@ERROR <> 0
ROLLBACK TRANSACTION
END CATCH
Whenever i tried execute it in SQL server, the script ran but somehow it won't commit the transaction. I still have to manually commit with the COMMIT TRANSACTION.
In my SQL Server Management Studio 2012, i did checked the SET_IMPLICIT_TRANSACTIONS to avoid autocommit when developing the script. But since i already put the statement COMMIT TRANSACTION if all statements ran successfully, then by right my script should autocommit right? Unless if i got mistaken here.
Split Number string into temp table in SQL server
I want to split string like this '1,2,3,4,5,6,7,8,9,10,11,12'
I found code in Internet and I modifier to my work
DECLARE @inpMonth NVARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,11,12'
DECLARE @lastYear INT = 2010
DECLARE @delimeter NVARCHAR(1) = ','
DECLARE @mname NVARCHAR(30)
CREATE TABLE #tmp2 (label INT,yy NVARCHAR(100),fromMonth INT,toMonth INT,link INT,mName NVARCHAR(30))
WHILE LEN(@inpMonth) > 0
BEGIN
DECLARE @TYear NVARCHAR(100)
DECLARE @chidx BIGINT = CHARINDEX(@delimeter,@inpMonth)
IF CHARINDEX(@delimeter,@inpMonth) > 0
BEGIN
SET @TYear = SUBSTRING(@inpMonth,0,CHARINDEX(@delimeter,@inpMonth,0))
END
ELSE
BEGIN
SET @TYear = @inpMonth
SET @inpMonth = ''
END
SET @mname = CASE @TYear
WHEN '1' THEN 'Jan'
WHEN '2' THEN 'Feb'
WHEN '3' THEN 'Mar'
WHEN '4' THEN 'Apr'
WHEN '5' THEN 'May'
WHEN '6' THEN 'Jun'
WHEN '7' THEN 'Jul'
WHEN '8' THEN 'Aug'
WHEN '9' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Sep'
WHEN '12' THEN 'Dec'
END
INSERT INTO #tmp2 VALUES (@lastYear + 543, @lastYear,@TYear,@TYear, 1,@mname)
SET @inpMonth = REPLACE(@inpMonth,@TYear + @delimeter , '')
END
SELECT *
FROM #tmp2
DROP TABLE #tmp2
Then when I run this code I get record 112 instead 11 and 12 because at end of while statement
REPLACE() are replace '1,' (in first loop) in @inpMonth Like this
REPLACE('1,2,3,4,5,6,7,8,9,10,11,12','1,','')
I get this Result 2,3,4,5,6,7,8,9,10,112
How to I get correct like this '2,3,4,5,6,7,8,9,10,11,12'
Thank you !
How to save image in SQL image type column from picturebox without mentioning pics url?
I have a picturebox where custom image is drawn when mouse button is pressed now i want to save that image in SQL's image type column .
I did search for saving but theres nothing for saving a pic from picturebox to sql image type column without url .
Create Asymmetric key in SQL using a variable password (from stored procedure)
create procedure SP_INS_PUBLIC_NHANVIEN
@manv varchar(20),
@hoten nvarchar(100),
@email varchar(20),
@luongcb varbinary ,
@tendn nvarchar(100),
@mk varchar
as
create asymmetric key mahoaluongi
with algorithm = RSA_512
encryption by password = @mk
insert into nhanvien values (@manv,@hoten,@email,ENCRYPTBYASYMKEY(mahoaluongi,@luongcb),@tendn,HASHBYTES('SHA1',@mk),@manv)
drop asymmetric key mahoaluongi
go
I want to create a new asymmetric key with new password each time, passed from the parameter of stored procedure. The issue is : "incorrect syntax near @mk. expecting STRING or TEXT_LEX." but it was impossible to convert from varchar to STRING using "cast" or "convert" I'm using SQL server 2014
Select statement vs Dropdown list variable in SQL
I'm currently havin an issue with the following code:
<form action="" method="post">
<input type="text" name="term" placeholder="Search Terms"/>
<select id=""drop"" name="drop"">
<option value="CODE">Code</option>
<option value="SCINAME">Scientific Name</option>
<option value="COLLECTOR">Holder</option>
<option value="DATA">Data</option>
</select>
<br/>
<button type="submit" class="button primary">Search</button>
</form>
<?php
if (!empty($_REQUEST['term'])) {
$term = mysql_real_escape_string($_REQUEST['term']);
$drop = ($_REQUEST['drop']);
$sql = "SELECT * FROM ANIMAIS WHERE '%".$drop."%' LIKE '%".$term."%'";
$r_query = mysql_query($sql);
while ($row = mysql_fetch_array($r_query)){
echo '<br />code: ' .$row['CODE'];
echo '<br />Name: ' .$row['SCINAME'];
echo '<br /> Colector: '.$row['COLLECTOR'];
echo '<br /> Local: '.$row['LOCAL'];
echo '<br /> Data: '.$row['DATA'];
echo '<br /> Descr: '.$row['DESCRIPTION'];
echo '<br />';
}
}
?>
The problem is that the code does not "run" the $drop variable, but if i remove the dropdown list from the form and change the following line:
$sql = "SELECT * FROM ANIMAIS WHERE '%".$drop."%' LIKE '%".$term."%'";
to
$sql = "SELECT * FROM ANIMAIS WHERE SCINAME LIKE '%".$term."%'";
It does work, but searching on the SCINAME column, what i wan't do is allowing the dropdown list to select wich SQL column to query, but the actual solution isn't working so well.
Thanks in advance for all help.
SQL Syntax Error When Importing .sql file from mysql 4.0
I am trying to import a .sql file that was created with mysql 4.0 into the latest version of mysql and have received a syntax error regarding the following code:
CREATE TABLE edgemap (
senderid int(10) unsigned default NULL,
recipientid int(10) unsigned default NULL,
messageid int(10) unsigned default NULL,
messagedt timestamp(14) NOT NULL,
reciptype enum('bcc','cc','to') default NULL,
subject varchar(255) default NULL,
KEY senderid (senderid,recipientid),
KEY messageid (messageid),
KEY messagedt (messagedt),
KEY senderid_2 (senderid),
KEY recipientid (recipientid)
) ENGINE=MyISAM;
The error message I receive is:
ERROR 1064 (42000) at line 255752: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(14) NOT NULL,
reciptype enum('bcc','cc','to') default NULL,
subject varchar' at line 5
Any help would be much appreciated!
How do I write a Stored Procedure to create a cart in a database?
Hi i want to eventually write code so that a user can add items to a shopping cart on my music website. Users should be able to add/delete items from their cart while logged in, the cart should not clear (even if user logs out) until the an order is placed. The cart table has a primary key set on (CartID, CustID)
From what I understand: 1st I want to create a cart for the particular user, This is the procedure I wrote to create cart, but i'm not sure if this is how I should do it, do I need to check if cart already exists? Is this a good way to create a cart?:
CREATE PROCEDURE usp_Cart_create_cart
-- Add the parameters for the stored procedure here
(
@custID float
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
insert into Cart (CartID, CustID)
values (+1, @custID)
END
Separate question: Can I create cart and add to cart all in one procedure?
SQL update row depending on other values in same row
I have a table similar to this:
Index Name Type
--------------------------------
1 'Apple' 'Fruit'
2 'Carrot' 'Vegetable'
3 'Orange' 'Fruit'
3 'Mango' 'Fruit'
4 'Potato' 'Vegetable'
and would like to change it to this:
Index Name Type
--------------------------------
1 'Apple' 'Fruit 1'
2 'Carrot' 'Vegetable 1'
3 'Orange' 'Fruit 2'
3 'Mango' 'Fruit 3'
4 'Potato' 'Vegetable 2'
Any chance to do this in a smart update query (= without cursors)?
Display the result of a query in a JTextField's value
I want to display the new id from database in a jTextField but it dosen't.. this is my code
public void actionPerformed(ActionEvent e) {
try {
DB con=new DB();
con.connecte();
String req = "SELECT Max(num) FROM condidat";
ResultSet rs = stmt.executeQuery(req);
int num = rs.getInt("num");
int nvNum=num+1;
txt_num.setText(valueOf(nvNum));
}
catch (ClassNotFoundException ex) {
Logger.getLogger(Ajouter.class.getName()).log(Level.SEVERE, null, ex);
}
catch (SQLException ex) {
Logger.getLogger(Ajouter.class.getName()).log(Level.SEVERE, null, ex);
}
}
//To change body of generated methods, choose Tools | Templates.
}
How to get this sql query: show records by amount
I want to display amount of sales from a table in this format:
Sales qty
------------------------------------------
Sales > 50,000 500
Sales < 2000 200
Sales > 15,000 600
------------------------------------------
How can I create this ? I can write a query for one amount where > 50000 but in this case there is three 50,000 , 2000, 15,000 and how can I show all this in the result?
what happens with this sql code?
I didn't understand what the following code does, I am new to sql, can some one explain in detail?
A = SELECT column1, column2, column3, column4;
B = SELECT column1, SUM(column3) AS Catcol3, SUM(column4) AS Catcol4;
Col1Summed = SELECT A.*, Catcol3, Catcol4 FROM A INNER JOIN B ON A.column1 == B.column1;
OLEDB SQL Syntax Error, I can't see it
I am trying to send a new user on my C# program to the database (MSAccess in this case). I create the string as follows...
String StrCmd = String.Format("INSERT INTO tbl_Users (Username, Password, IsAdmin) VALUES ('{0}', '{1}', {2});", Username, passwordHash, AdminPower);
This creates an SQL string, for example...
INSERT INTO tbl_Users (Username, Password, IsAdmin)
VALUES ('TestUser', '1013638657', False);
This works fine if I paste it into a Query in MSAccess, but in the code I keep getting a Syntax error in INSERT INTO statement. error.
Can anyone see something obvious I am missing?
Additional Information:
Username and passwordHash are Strings, AdminPower is a Boolean.
Username and Password are Text Fields, IsAdmin is a Yes/No Field.
Code Block of interest...
OleDbConnection MyConn = new OleDbConnection(Program.ConnStr);
MyConn.Open();
String StrCmd = String.Format("INSERT INTO tbl_Users (Username, Password, IsAdmin) VALUES ('{0}', '{1}', {2});", Username, passwordHash, AdminPower);
OleDbCommand Cmd = new OleDbCommand(StrCmd, MyConn);
Cmd.ExecuteNonQuery();
MyConn.Close();
Changing the path of the image inside the database
I found this coding on how to add image into the database and it is working perfectly fine. However, I would like to change the location of the image into a different folder/path.
So, when i add "../" to change the path it won't work.
Below is the coding that path has been changed.
<?php
include('dbconn.php');
if (!isset($_FILES['image']['tmp_name'])) {
echo "";
}
else{
$file=$_FILES['image']['tmp_name'];
$image= addslashes(file_get_contents($_FILES['image']['tmp_name']));
$image_name= addslashes($_FILES['image']['name']);
move_uploaded_file($_FILES["image"]["tmp_name"],"../photo/" . $_FILES["image"]["name"]);
$location="../photo/" . $_FILES["image"]["name"];
$emp_id=$_POST['emp_id'];
$save=mysql_query("INSERT INTO photo (location, emp_id) VALUES ('$location','$emp_id')");
if( $save ) {
echo "<script type='text/javascript'>
alert('Successfully saved!');
window.history.back();
</script>";
}
else {
echo "<script type='text/javascript'>alert('failed!')</script>";
}
exit();
}
This is the path I want it to be in my database.
The path inside my database after I added ../ into the coding.
Storing expirable credits in database?
It is easy to store user credit with an extra col in the user table, e.g. user_credits, now an extra requirement
- for each credit added to the
user_creditscol, it will auto expire one year after if not being consumed.
Definitely I need an extra table for storing expiring information, e.g.
[table credits_history]
user_id
credits
used
created_at
So, when consuming credits, I need to
- check if the
user_creditsis enough to consume - loop all
credits_historytable for the user's credit whichused = 0 AND now - created_at < 1yr, and setused to 1 - update the
user_credits
Finally I need to set a daily cron job to update user_credits by looking at the created_at
Are the above approach reasonable? Or any standard way to handle the above requirements?
Best way to relate the order ID with the products that belongs to it
I'm developing a shop system and I'm using the only method I was able to find to relate the ID of the order with the products that belongs to it.
When someone purchase something, first the order is added to the table orders with their details, including the ID of that order (order_id), that is auto incremented by the SQL.
I use this to add an order to the table orders:
INSERT INTO orders SET customer_id = '{$customer_id}', customer_name = '{$user['customer_name']}', order_price = '{$total_price}', order_date = '{$date}'"
Ok, the order was added. Now, in sequence, the products that belongs to that order will be added to another table, the table purchased_products.
I use PDO lastInsertId() to get the last inserted order_id from the table orders and then add each product of that order with a Foreign Key order_id in another table called purchased_products. To do this, I use:
$respective_order_id = $connection->lastInsertId();
foreach($_SESSION['cart'] as $product)
{
sql = "INSERT INTO purchased_products SET order_id = '{$respective_order_id}', product_name = '{$product['product_name']}', product_price = '{$product['product_price']}', quantity = '{$product['quantity']}'";
}
These codes run simultaneously. First the order will be added in the orders table with their order_id auto incremented, and then all the products of that order will also be added to the purchased_products table and the Foreign Key order_id of each one of them will have the value of the last order_id inserted in the orders table. Later I will can display any order with their products by consulting it with the Foreign Key order_id.
So far, it's working well. And as I said, this was the only way I found to assign the ID of the order with the products that belongs to it. My question is: is this secure? How about if several people buy the same time? There is the risk of the IDs be exchanged or the products not added/or goes to the wrong order? I would be immensely grateful if someone experienced answer these questions because this is making me afraid, I'm wondering if I can trust in this method.
Including 0 On Count when Filtering on SQL
Listed below is my code. It returns the count of each category that a person has been in for a movie. It returns the result but I would like it to list every category including the ones with 0 counts. I tried every combination of LEFT JOIN, OUTER JOIN, etc and it still doesn't work. Any help would be appreciated!
SELECT c.name, COUNT(f.title) FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
INNER JOIN film f ON fc.film_id = f.film_id
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id
WHERE a.first_name = 'Jack' AND a.last_name = 'Daniel'
GROUP BY c.name ASC;
Capturing mutliple XML strings with the same node names in SQL
Weaving my way through the XML string world - I've come across this issue I'm having.
So I have two XML string that are super similar to each other - only thing is - is that they have different info inside the nodes.
XML string 1:
<DocumentElement>
<Readings>
<ReadingID>1</ReadingID>
<ReadingDate>2013-12-19T00:00:00-05:00</ReadingDate>
<Sys>120</Sys>
<Dia>80</Dia>
<PageNumber>4</PageNumber>
<AddedDate>2015-04-17T19:30:22.2255116-04:00</AddedDate>
<UpdateDate>2015-04-17T19:30:22.2255116-04:00</UpdateDate>
</Readings>
<Readings>
<ReadingID>2</ReadingID>
<ReadingDate>2014-01-10T00:00:00-05:00</ReadingDate>
<Sys>108</Sys>
<Dia>86</Dia>
<PageNumber>8</PageNumber>
<AddedDate>2015-04-17T19:32:08.5121747-04:00</AddedDate>
<UpdateDate>2015-04-17T19:32:08.5121747-04:00</UpdateDate>
</Readings>
</DocumentElement>
XML String 2:
<DocumentElement>
<Readings>
<ReadingID>1</ReadingID>
<ReadingDate>2013-12-20T00:00:00-05:00</ReadingDate>
<Sys>140</Sys>
<Dia>70</Dia>
<PageNumber>10</PageNumber>
<AddedDate>2015-04-17T19:30:22.2255116-04:00</AddedDate>
<UpdateDate>2015-04-17T19:30:22.2255116-04:00</UpdateDate>
</Readings>
</DocumentElement>
Now this is really just an example - I could have an infinite amount of strings just like this that I would want to pull data from. In this case I have two strings and I'm looking to extract all info on <Sys>, <Dia> and <ReadingDate>
I would also like to display this info in a table like this:
Reading Date | Sys | Dia
----------------------------
12/29/2013 | 120 | 80
----------------------------
1/10/2014 | 108 | 86
----------------------------
12/20/2013 | 140 | 70
I am totally unsure how to proceed with this - any and all help is appreciated!
Selecting all info from nodes with the same name
I'm a total newbie when it comes to xml stuff.
So far I have this piece of xml that I want to extract info from, but all the node names are the same (so it just grabs one of them, unless stated otherwise).
It looks something like this:
<DocumentElement>
<Screening>
<ScreeningID>2</ScreeningID>
<ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
<ScreeningResult>1</ScreeningResult>
<ScreeningResultText>Negative</ScreeningResultText>
<TextResult>0</TextResult>
<TextResultText>Not Tested</TextResultText>
<PageNumber>0</PageNumber>
<AddedDate>2015-05-03T16:06:41.71774-04:00</AddedDate>
<UpdateDate>2015-05-03T16:06:41.71774-04:00</UpdateDate>
</Screening>
<Screening>
<ScreeningID>3</ScreeningID>
<ScreeningDate>2011-09-13T00:00:00-04:00</ScreeningDate>
<ScreeningResult>1</ScreeningResult>
<ScreeningResultText>Negative</ScreeningResultText>
<TextResult>1</TextResult>
<TextResultText>Negative</TextResultText>
<PageNumber>9</PageNumber>
<AddedDate>2015-05-03T16:25:21.2904988-04:00</AddedDate>
<UpdateDate>2015-05-03T16:25:21.2904988-04:00</UpdateDate>
</Screening>
And I'm currently using this kind of snippet to extract info from the TextResult area
Select
answer.value('(/DocumentElement/Screening/TextResult)[1]','int')
From
Answers
However, that only grabs the first bit of info, I know that if I write something like this, it'll get me the second bit of info but on another column: answer.value('(/DocumentElement/Screening[2]/textResult)[1]','int')
I have two issues with this: 1. There isn't necessarily going to be only 2 nodes with the same name - it could go on infinitely. And 2. I would like all the info to be gathered into only one column.
Any help would be appreciated!
SQL server 2008R2 shutting down(0x80041033)
I installed the SQL Server 2008R2, but this error report. system: Windows 7 Please help to look at, thank you ! online etc.
How do I use a rowfilter with a list?
So I have a checkboxlist that I have loaded with values from a datatable, which I then bind to a dataview. I'm trying to filter the dataview based on whatever the user checks in the checkboxlist. Since they can select more than one value, how do I write the rowfilter to use this list which has multiple selected values? If there is a better method then using a list I am open to suggestions, thanks in advance :)
dim GenreList as string = "("
for item as integer = 0 to CheckListBox.count-1
GenreList += "'" & CheckListBox.checkedItems(Item) & "',"
next
GenreList = GenreList.substring(0,GenreList.Length-1) + ")"
Myview.RowFilter = "Genre = "
select slow because of unused inner join
I have two tables:
CREATE TABLE `A` (
`id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
CREATE TABLE `B` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a_id` int(11) NOT NULL,
`c_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `IX_a_id` (`a_id`),
KEY `IX_c_id` (`c_id`),
CONSTRAINT `a_id_ibfk_1` FOREIGN KEY (`a_id`) REFERENCES `A` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
They have a couple million rows each.
explain select count(*) FROM B inner join A on B.a_id = A.id WHERE B.c_id = 7;
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
| 1 | SIMPLE | B | ref | IX_a_id,IX_c_id | IX_c_id | 4 | const | 116624 | Using where |
| 1 | SIMPLE | A | eq_ref | PRIMARY | PRIMARY | 4 | test1.B.a_id | 1 | Using index |
+----+-------------+-------+--------+-----------------------+------------+---------+--------------------+--------+-------------+
Now, I can't understand why mysql is unable to ignore the un-needed inner join to A which kills performance. i.e., the following query is equivalent to the above:
select count(*) from B where B.c_id = 7
which should be easy to infer since B.a_id can't be null and B.a_id has a constraint to the unique key A.id
Is there a way to make mysql understand this ?
SQL Server 2012: Select literal and compare to the literal in Where
So, basically, in my query, I want to have a statuses column that will contain 1 or more statuses based on certain criteria. A user should be able to filter/search also on those status messages in that column. I'm not sure on the syntax to make this happen.
This selects 0 to 2 status divs in my column, which I display as is.
Here's theoretically what I want to happen:
Select fields, (select CASE WHEN root_directory IS NULL THEN '<div class="gray status">Unbuilt</div>' ELSE '' END + ' ' + (select top(1) CASE WHEN isnull(payment_received,0) = 1 THEN '' ELSE '<div class="red status">Unpaid</div>' END
FROM invoice C
WHERE C.id = B.id) as statuses
FROM table
WHERE statuses LIKE '%Unbuilt%'
Thoughts on how to WHERE LIKE my statuses column?
Thanks.
Sum of Distinct count in Oracle
I have the following table:
CUST_PRODUCT_DTL
Cust_ID Product_ID QTY
1 10 5
2 10 2
3 10 5
1 11 5
2 12 1
How can I get Total Distinct CUST_ID, TOTAL DISTINCT PRODUCT_ID from the above table in Oracle 11 G
The below one doesn't work
SELECT SUM(COUNT(DISTINCT cust_id)), product_id
FROM CUST_PRODUCT_DTL
WHERE
GROUP BY product_id , cust_id
SQL Project Help - Using Oracle - Order Entry screen
I'm trying to write the SQL code for oracle sql developer, to output the code below. If someone is willing to help me, I'll also post the file that creates all of the tables and rows with data. thanks!
A sample Order Entry Transaction is as follows: The bold, italic, underlined text indicates user input.
@ c:\neword ********** Order Entry Screen **********
Date: 13-APR-15
Enter Part Number (format 999): 101 Part Description: Air Filter Quantity in stock: 120
Enter Supplier Code (format 999): 102 Address: 456 Main Street City, State Zip: Huntington Beach, CA 92647 Phone: (714) 555-2222
Enter Quantity to Order: 20
Your order has been processed. Order number is: 1010
If there are errors in the order entry process (Order placed with a supplier that is not approved for the part, supplier does not exists, part number does not exist, etc), the order should NOT be processed and the confirmation message should NOT be displayed.
Below is an example of an order placed with a supplier that is not approved for the part:
@ c:\neword ********** Order Entry Screen **********
Date: 13-APR-15
Enter Part Number (format 999): 101 Part Description: Air Filter Quantity in stock: 120
Enter Supplier Code (format 999): 104 Address: 444 Auto Way City, State Zip: Long Beach, CA 90840 Phone: (562) 555-4444
Enter Quantity to Order: 20
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE) * ERROR at line 1: ORA-02291: integrity constraint (ORD_PARTNUM_SUPPCODE_FK) violated - parent key not found
Below is an example of the error messages that should be displayed when the user enters characters instead of number for part number, supplier code, or quantity (the error also appears if part numbers or supplier numbers are more than three digits). Also, notice the information displayed for non-existent parts and/or supplier codes.
Make sure to display similar error messages in your program.
@ c:\neword ********** Order Entry Screen **********
Date: 13-APR-15
Enter Part Number (format 999): Hello SP2-0598: "Hello" does not match input format "999" Enter Part Number (format 999): 9999 SP2-0598: "9999" does not match input format "999" Enter Part Number (format 999): 250 Part Description: Part does not exist Quantity in stock: N/A
Enter Supplier Code (format 999): Test SP2-0425: "Test" is not a valid NUMBER Enter Supplier Code (format 999): 1 Address: Supplier Not Found City, State Zip: NA Phone: NA
Enter Quantity to Order: Ten SP2-0425: "Ten" is not a valid NUMBER Enter Quantity to Order: 10
INSERT INTO ORD (ORD_NUM, PART_NUM, SUPPLIER_CODE, ORD_QTY, ORD_DATE) * ERROR at line 1: ORA-02291: integrity constraint (.ORD_PARTNUM_SUPPCODE_FK) violated - parent key not found
Select @xml.nodes ... how to assign column?
I want to put result of my xml.node sql into table and ran into this small problem like missing column name, can anybody recomend me. This will break single file xml into nodes, one node per one row.. this sample below works OK, but I need activate commented lines somehow:
--CREATE TABLE TSxml (TSNum INT IDENTITY (1,1), xmlinfo XML)
--SELECT * FROM TSxml
declare @xml XML = (select c1 from t3)
--INSERT INTO TSxml
--SELECT * FROM (
SELECT T.N.query('.')
FROM @xml.nodes('/testsuite') as T(N)
--) AS C1
MS Access Substring
I created a search Form for my db. It works great, if you enter everything in 100% the same as the db has it stored. What I want to change is the 100% part. If I enter "Jones" in the address tect box and hit search, I want "123 Jones", "497 Jones", etc. to appear in my query. Is it possible to do this?
This is my code. Only thing missing is more of the same basic if statements.
ElseIf sA <> "" Then
qry = "Address"
DoCmd.OpenQuery qry
SQL query: How to retrieve the values of a column which are not located on both hosts
I have a studynumber and a studyhost.
I also have a table view which combines the 2 in a nice view. But to really get to the studyhost a few joins are needed. So this is what i want. From that view I need to know which studynumbers are located in 1 of the 2 hosts.
Every studynumber is duplicate stored on 2 separate hosts, one the storage other the mirror.
So we have s1 and m1, s2 and m2, s3 and m3
The study number should be located on both s and m host.
Now i seek a way to find out which studynumbers are located on only the m hosts and not on s hosts and visa versa.
SO i can make a select * from statement but I cannot say where studynumber = 123 and where studynumber is on host m and host s.
Any assistance is greatly appreciated.
image patch upload to sql
I am trying to upload to SQL database the name of the uploaded image (for example : test.png) using DW Server behaviors
I am getting this error message:
** Notice: Undefined index: upload in C:\xampp\htdocs\Foglalo\pages\addRooms.php on line 95 Column 'img' cannot be null" **
<?php
if (!fun
ction_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
if (PHP_VERSION < 6) {
$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
}
$theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
switch ($theType) {
case "text":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "long":
case "int":
$theValue = ($theValue != "") ? intval($theValue) : "NULL";
break;
case "double":
$theValue =
($theValue != "") ? doubleval($theValue) : "NULL";
break;
case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
case "defined":
$theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
break;
}
return $theValue;
}
}
$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
$editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "addRooms")) {
$insertSQL = sprintf("INSERT INTO rooms (RoomName, City, Street, Postcode, RoomType, img) VALUES (%s, %s, %s, %s, %s, %s)",
GetSQLValueString($_POST['roomname'], "text"),
GetSQLValueString($_POST['city'], "text"),
GetSQLValueString($_POST['street'], "text"),
GetSQLValueString($_POST['postcode'], "text"),
GetSQLValueString($_POST['roomtype'], "text"),
GetSQLValueString($_POST['upload'], "text"));
mysql_select_db($database_Foglalo, $Foglalo);
$Result1 = mysql_query($insertSQL, $Foglalo) or die(mysql_error());
}
mysql_select_db($database_Foglalo, $Foglalo);
$query_addRoom = "SELECT * FROM rooms";
$addRoom = mysql_query($query_addRoom, $Foglalo) or die(mysql_error());
$row_addRoom = mysql_fetch_assoc($addRoom);
$totalRows_addRoom = mysql_num_rows($addRoom);
?>
my form
<form action="<?php echo $editFormAction; ?>" method="POST" enctype="multipart/form-data" name="addRooms" id="addRooms">
<table width="600" align="center" class="TableRightBorder">
<tbody>
<tr>
<td width="119">Room name</td>
<td width="200"><input type="text" name="roomname" id="roomname" /></td>
<td width="265"> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">City</td>
<td><input type="text" name="city" id="city" /></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">Street</td>
<td><input type="text" name="street" id="street" /></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">Postcode</td>
<td><input type="text" name="postcode" id="postcode" /></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">Room type</td>
<td><select name="roomtype" id="roomtype">
<option value="room">Room</option>
<option value="office">Office</option>
</select></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">Room image</td>
<td><input type="file" name="img" id="upload" /></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20">Description</td>
<td><textarea name="textarea" id="textarea" cols="30" rows="5"></textarea></td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td height="20"> </td>
<td><input name="submit" type="submit" class="right" id="submit" value="Add room" /></td>
<td> </td>
</tr>
</tbody>
</table>
<input type="hidden" name="MM_insert" value="addRooms" />
</form>
How to structure this relational database
Basically I'll be having 3 tables that have relation. They are: users, departments and company.
The issue I have is this:
- A
companycan have many departments - A
departmentcan only be attached to one company - A
usercan only be part of one company - A
usercan be part of many departments
This is essentially what the table relation would look like:
____________________
| | | | |
| | | | |
-------- -------------- -----------
| user | | department | | company |
-------- -------------- -----------
| | | | | | | |
| | | | | | | |
| ___________________ |
| |
| |
| |
_______________________________________
The above multiple | lines show an option, so the "company" above has 4 departments and so on.
Now my question is this, How should I structure the relation tables?
Should I have user_departments, user_company and company_departments tables?
That would essentially look like this:
--------------------
| user_departments |
--------------------------------
| id | user_id | department_id |
--------------------------------
----------------
| user_company |
-----------------------------
| id | user_id | company_id |
-----------------------------
-----------------------
| company_departments |
-----------------------------------
| id | company_id | department_id |
-----------------------------------
Or are there any other alternatives for me to consider/implement instead of the path I'm going as it seems it'll just keep growing complex?
How to write SQL result to JSON
I have this java code that writes the first record of the SQL result set to output in JSON using Google GSON.
But when the result set has more than 1 record it throws exception as "JSON must have only one top-level value"
HttpServletResponse httpres=response;
httpres.setContentType("application/json; charset=UTF-8");
httpres.setCharacterEncoding("UTF-8");
JsonWriter writer = new JsonWriter(new OutputStreamWriter(httpres.getOutputStream(), "UTF-8"));
while(rs.next()) {
writer.beginObject();
// loop rs.getResultSetMetadata columns
for(int idx=1; idx<=rsmd.getColumnCount(); idx++) {
writer.name(rsmd.getColumnLabel(idx)); // write key:value pairs
writer.value(rs.getString(idx));
}
writer.endObject();
}
writer.close();
httpres.getOutputStream().flush();
Are these SQL statements correct..?
I'm working on a sql project and I'm not sure if I'm answering the questions correctly. I don't have the schema or table with me at the moment and I just want a general critique on whether or not what I'm doing is correct.
QUESTIONS:
-
List the first name, last name, sales rep ID, commission class, and commission rate for all Sales Reps. Concatenate the first and last names together. Sort by last name in ascending order, and use the following column headings: SalesRep_Name, Sales_Rep_ID, Commission_Class, Commission_Rate.
-
List all rows and all columns from OrderDetail; sort by Order ID then by Product ID, both in ascending order; format the Product Price as currency.
-
For all customers, list the customer ID, customer first name, customer last name, and customer phone number, along with the sales rep ID, sales rep first name, and sales rep last name of the sales rep to whom that customer belongs; sort by Customer ID in ascending order; format the phone number as ‘(###) ###-####’ by using concatenation and the SUBSTR function. Use the following column headings: CustID, CustFirstName, CustLastName, CustPhone, SalesRepID, SalesRepFirstName, SalesRepLastName.
-
List the department ID, department name, sales rep ID, first name, last name, commission class, and commission rate of the sales rep(s) who earn the highest commission in each department. Use the following column headings: Dept_ID, Dept_Name, Sales_Rep_ID, First_Name, Last_Name, Commission_Class, Commission_Rate.
SQL statements:
--question 1
SELECT (SalesLName || ' , ' || SalesFName) AS SalesRep_Name, SalesRepID AS Sales_Rep_ID, salesR.Comm_Class AS Commission_Class, Comm_Rate AS Commission_Rate
FROM Sales_Report_exp salesR, Commission_exp Com
WHERE salesR.Comm_Class = Com.Comm_Class
ORDER BY salesR.SalesLName ;
--question 2
SELECT ORDT.OrderID AS Order_ID, Pro.ProdID AS ProductID,to_char(ProdPrice,'$99.99') AS ProductPrice
FROM Order_Detail_mys ORDT, Product_mys Pro
WHERE ORDT.ProdID = Pro.ProdID
ORDER BY ORDT.OrderID ASC, Pro.ProdID ASC;
--question 3
SELECT Cus.CustID AS CustID,CustFName AS CustFirstName,CustLName AS CustLastName,('(' || SUBSTR(Cus.CustPhone,1,3) || ')' || SUBSTR(Cus.CustPhone,4,3) || '-' || SUBSTR(Cus.CustPhone,7,4)) AS CustPhone,salesR.SalesRepID AS SalesRepID,SalesFName AS SalesRepFirstName,SalesLName AS SalesRepLastName
FROM Customer_mys Cus,Sales_Report_mys salesR
WHERE Cus.SalesRepID=salesR.SalesRepID
ORDER BY CustID;
--question 4
SELECT DeP.Dept_ID AS Dept_ID, Dept_Name AS Dept_Name, salesR.SalesRepID AS Sales_Rep_ID,
SalesFName AS First_Name, SalesLName AS Last_Name,
CoM.Comm_Class AS Commission_Class , Comm_Rate AS Commission_Rate
FROM Department_mys DeP, Sales_Report_mys salesR, Commission_mys CoM
WHERE DeP.Dept_ID = salesR.Dept_ID AND CoM.Comm_Class = salesR.Comm_Class
AND (DeP.Dept_ID,CoM.Comm_Rate) IN (SELECT DeP.Dept_ID,MAX(CoM.Comm_Rate) FROM Sales_Report_mys salesR, Commission_mys CoM, Department_mys DeP
WHERE CoM.Comm_Class = salesR.Comm_Class
AND DeP.Dept_ID = salesR.Dept_ID
GROUP BY DeP.Dept_ID);
2 Many to Many relationship - extract data with join
Please check the schema in the link below.
I want to select all properties and also the agent and the owner of each property. The problem is that both agent's and owner's details comes from the same table and there is a conflict.
What solution should i follow?
Note: Unfortunately I cannot change the db schema.
The connection variable in mysqlli_query is not defined
I have the following problem is that $con is not defined, despite i use it to connect to the database. I don't understand why is that.
<?php
define("DB_HOST", "localhost");
define("DB_NAME", "Bar_buddy_users");
define("DB_USER", "local_server");
define("DB_PASSWORD","010203");
error_reporting( E_ALL );
ini_set('display_errors', 1);
$con = new mysqli(DB_HOST,DB_USER,"010203",DB_NAME) or die("Failed to connect to database");
if ($con->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
function NewUser(){
$first_name= $_POST["firstname"];
$last_name = $_POST["lastname"];
$email = $_POST["email"];
$password = $_POST["password"];
$query = "INSERT INTO Users (firstname, lastname, email, password) VALUES ('$first_name', '$last_name', '$email', '$password')";
mysqli_query($con,$query);
mysqli_close($con);
}
?>
SQL Server triggers - Update one field one time once another isn't null
I'm fairly new to triggers and I'm trying to update Field1 if it's null with the value from Field2 as long as Field2 isn't null on an update or insert. In other words, I want to retain the first non null value from "NonAccrual" and store it in "OriginalNonAccrual". Once OriginalNonAccrual gets a value, it should remain the same indefinitely even if NonAccrual changes. I don't know if one trigger can handle both an insert and an update and hopefully the code below gives you the idea of what I'm trying to do. Thank you!
CREATE TRIGGER tr_SAG_Accrual
ON [dbo].[SAG]
AFTER INSERT, UPDATE
AS
BEGIN
Declare @NonAccrual date
Declare @OriginalNonAccrual date
Select @NonAccrual = NONACCRUAL_DATE
, @OriginalNonAccrual = OriginalNonAccrualDate
from inserted
IF @OriginalNonAccrual IS NULL AND NOT @NonAccrual IS NULL
SET @OriginalNonAccrual = @NonAccrual
END
Creating Hourly Counts Across Different Shifts
I'm trying to create a query that divides a shifts production into hourly counts that can be ran through multiple shifts yet have the first hours of each shift fall under the same counts.
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '1' THEN quantity_increment_D ELSE 0 END) C1,
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '2' THEN quantity_increment_D ELSE 0 END) C2,
Sum(CASE WHEN DATEPART(HOUR,[creation_time]) = '3' THEN quantity_increment_D ELSE 0 END) C3,
This query works fine, in my report, for the first shift of the day, but second and third shifts won't restart on C1. I essentially need to ask something to the effect of:
If(shift_id_S)='1' then sum(quantity_increment_D) where (creation_time) between '00:00:00' and'07:59:59' as C1
If(shift_id_S)='2' then sum(quantity_increment_D) where (creation_time) between '08:00:00' and'15:59:59' as C1
If(shift_id_S)='3' then sum(quantity_increment_D) where (creation_time) between '16:00:00' and'23:59:59' as C1
If(shift_id_S)='4' then sum(quantity_increment_D) where (creation_time) between '00:00:00' and'11:59:59' as C1
If(shift_id_S)='5' then sum(quantity_increment_D) where (creation_time) between '12:00:00' and'23:59:59' as C1
If I can get help with this part of the query, I can take care of the rest of the counts.
Java reading an online dictionary(oracle sql)
I need a little help with a program i had an idea about. I wanted to make an anagram guesser eg. giving the letters BTA would return BAT and TAB. However i wanted to import an online dictionary to an array (using sql) so i could begin using that to guess which words the unsorted letters could be. Any ideas/links would be grateful, as i really have no idea where to begin(im still in school btw).
SQL 2008: get distinct values in a table
I have a small temp table with 2 columns: ErrorName and Value.
ErrorName|Value
---------|-----
Error1 |3
Error2 |2
Error3 |2
Error1 |1
Error2 |1
I want to sum up the value field based on the ErrorName and return something like this:
Error1 |4
Error2 |3
Error3 |2
Declare @ variable in SQL returning an error
I am trying to declare a variable in SQL. I Have tried both
declare @mean INT; set @mean = .5;
and
declare @mean INT set @mean = .5
I keep getting this error:
An unexpected token "" was found following "". Expected tokens may include: "declare @mean INT"
SQL query JOIN a table on itself (separate IDs and Titles tables)
Order table
o_id customer_id drink_id food_id
---- ----------- -------- -------
1 22 10 12
2 22 11 12
3 22 11 12
4 44 11 13
5 22 11 13
Title table
t_id type title
---- ---- -----
1 10 Black Velvet
2 11 Mojito
3 12 Rice
4 13 Meat
How to produce this result:
Given for example customer_id = 22
o_id drink_id drink_title food_id food_title
---- -------- ----------- ------- ----------
1 10 Black Velvet 12 Rice
2 11 Mojito 12 Rice
3 11 Mojito 12 Rice
5 11 Mojito 13 Meat
I dont know if the right way to do this:
- A nested
SELECTquery? - Or a
JOINon the order table on itself after a rename? ( if this is the case how should the query look like? )
Any help with this (occasionally including the sample query) would be realy appreciated.
Thanks! :)
Create an Community Members List From MySQL
How to create an members list from the mysql table. I used this code an it gives me an white page.
<?php
$sql = new mysqli('localhost','DB-USER','PASS','DB'); /
$sql -> set_charset ( 'utf8' );
if ($sql->connect_errno) {
printf("Connect failed: %s\n", $sql->connect_error);
exit();
}
while ($fetch = $profile_query_check->fetch (PDO::FETCH_ASSOC)) {
if ($fetch == NULL OR $fetch["id"]) {
break;
}
$username = $fetch['username'];
$avatar = $fetch['avatar'];
$id = $fetch['id'];
//echo the profile info
}
?>
And this the user table
CREATE TABLE IF NOT EXISTS `users` (
`id` int(50) NOT NULL,
`level` int(11) NOT NULL,
`username` varchar(50) NOT NULL,
`password` varchar(50) NOT NULL,
`email` varchar(50) NOT NULL,
`usertype` int(11) NOT NULL,
`rpname` varchar(50) NOT NULL,
`fbname` varchar(50) NOT NULL,
`avatar` varchar(100) NOT NULL,
`avatarchangedate` int(11) NOT NULL,
`new_activity_count` int(11) NOT NULL,
`online` tinyint(4) NOT NULL,
) ENGINE=MyISAM AUTO_INCREMENT=13397 DEFAULT CHARSET=utf8;
Please help me how to create the code? I'm noob
Update: But this whoisisonline code is working
<?php
if(!defined('A')){
include "error.php";die(header('HTTP/1.0 403 Forbidden'));
}
global $sql;
//http://ift.tt/1GXGfOs
//Checks the sesson file to see how many files there are
function usersonline() {
$sessionfiles = session_save_path() . "/sess*";
$usersonline = count(glob($sessionfiles));
return $usersonline;
}
if(isset(user::$current->id)){
$current_ID = user::$current->id;
}else{
$current_ID = "";
}
$sql->run("select_who_is_online", array(
"userID" => $current_ID,
"time" => (time() - 180)
));
// $sql->last_query_info();
$users = $sql->fetch_array();
$count = count($users);
$members_online = $count;
if (user::$loggedIn) {
$members_online++;
}
$vistors_online = (usersonline() - $members_online);
//plural check
if ($members_online == 0)
$members_online_message = "There are currently no NEETs online";
else if ($members_online == 1)
$members_online_message = "There is currently only one NEET online";
else
$members_online_message = "There are currently ".$members_online." NEETs online";
if($vistors_online == 1)
$vistors_online_message = "and ".$vistors_online." Person passing by";
else
$vistors_online_message = "and ".$vistors_online." People passing by";
echo "<div class='content_header blue small align_left' style='margin-top: 20px;'>Who is Online";
echo "<x style='float:right;margin-right:8px'>".$members_online_message.", ". $vistors_online_message ."<x>";
echo "</div>";
// echo "<h1 class='content_centered'>Who is Online</h1><br/>";
echo "<div class='content_padded content_centered content_feed light'>";
if($count){
foreach($users as $user){
// print_r($user);
$user_obj = new user($user["id"]);
$gUser = new gUser($user_obj);
$tmp = '';
$tmp .= "<div style='text-align: center; display: inline-block; width: 110px; margin-bottom: 10px;'>";
// if ($friend->online)
$tmp .= "<div class='basicSuccess' style='display: block; margin-bottom: 3px; width: 73px;'><i class='fa fa-wifi'></i> Online</div>";
$tmp .= $gUser->htmlAvatar(70, "middle");
$tmp .= "<div style='margin-top: 5px;'>" . $gUser->htmlSmall() . "</div>";
$tmp .= "</div>";
echo $tmp;
}
}else if(user::$loggedIn){
echo "<h5>No one else is online T^T</h5>";
}else {
echo "<h5>No one is online T~T</h5>";
}
echo "</div>";
?>
Inscription à :
Commentaires (Atom)