Jump to content

awjudd

Staff Alumni
  • Posts

    422
  • Joined

  • Last visited

  • Days Won

    1

Posts posted by awjudd

  1. You are missing the definition of what you are actually joining your two tables on as well as you need to prefix your tables in your WHERE clause (which is actually defining your join predicate).

    SELECT forename, surname, coursetitle
    FROM srs_student AS s
    INNER JOIN srs_course AS c ON s.coursecode = c.coursecode
    
  2. As mac_gyver said, you need to change $ID.  Right now it is the result from a query, you will need to first grab the row.  Or you can change your second query to join against the User table.

    <!DOCTYPE html>
    <html>
    <body>
    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    User<input type="text" name="user">
    <input type="submit">
    </form>
    <?php
     
    $user=$_POST['user'];
    if(isset($user)){
    
    // You need some SQL injection protection here
    $con=mssql_connect("SERVER","USER","PASSWORD");
    $item=mssql_query("select ItemID from PS_GameData.dbo.UserItem AS ui JOIN PS_UserData.dbo.Users AS u ON ui.UserUID = u.UserUID where UserID = '$user' order by MakeTime");
    }
    ?>
    </body>
    </html>
    
  3. Change your original query to grab a COUNT of the rows instead of the actual rows. Then if that count is > 0, then you do your update.

    <?php
    
    $id = ''; $name = "ed"; $year = 2013; $month = "may"; $wins = 48;
    
    if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());exit();}
    
    $stmt = $mysqli->prepare("SELECT COUNT(name) AS rowcount FROM compare WHERE name = ? AND month = ? AND year = ?");
    $stmt->bind_param('ssi', $name, $month, $year);
    $stmt->execute();
    
    $count=$stmt->get_result();
    printf("Affected rows (SELECT): %d\n", $count['rowcount']);
    
    if ($count['rowcount'] > 0) {    
    $stmt = $mysqli->prepare("UPDATE compare SET wins = ? WHERE name = ? AND month = ? AND year = ?");
    $stmt->bind_param('isii', $wins, $name, $month, $year);
    $stmt->execute();
    $stmt->close(); }
    
    else {
    $stmt = $mysqli->prepare("INSERT INTO compare VALUES (?, ?, ?, ?, ?)");
    $stmt->bind_param('isisi', $id, $name, $year, $month, $wins);
    $stmt->execute();
    $stmt->close(); }
    ?>
    
    Or you could use MySQL's built-in support for doing just that:

     

    http://www.kavoir.com/2009/05/mysql-insert-if-doesnt-exist-otherwise-update-the-existing-row.html

     

    ~awjudd

  4. Keep track of the 'previous' value (i.e. previous name) in a variable and then if the current value is the same as the previous value, don't emit it.

     

    $previous_name=NULL;
     
    foreach($array as $value)
    {
        if($value['name']!=$previous_name)
        {
            echo $value['name'];
            $previous_name=$value['name'];
        }
     
        // Display the rest
    }
     
    

  5. You copied my query wrong.  You included the aliased field in your backticks.  Because of that it is looking for `d.label` verbatim.

     

    Also your script is wrong because they don't come out with the alias up front, they come out with the names as they appears.

     

     

    <html>
    	<body>
    	<?php
    		$username="root";
    		$password="";
    		$database="db";
    		mysql_connect(localhost,$username,$password);
    		@mysql_select_db($database) or die( "Unable to select database");
    		$query="SELECT d.label, d.serial, d.exe, d.eal, d.epl, dt.Model, m.Manufacturer
    				FROM fac_Device AS d
    				JOIN fac_DeviceTemplates AS dt ON d.TemplateID = dt.TemplateID
    				JOIN fax_Manufacturer AS m ON dt.ManufacturerID = m.ManufacturerID";
    		$result=mysql_query($query)or die(mysql_error());
    		$num=mysql_numrows($result)or die(mysql_error());
    		mysql_close();
    	?>
    	<IMG SRC="http://localhost/dcim/images/logo.png"></IMG>
    	<h2><B>Report<BR></h2></B>
    	<b></B><BR><BR>
    	<table border="0" cellspacing="2" cellpadding="2">
    			<tr>	
    				<td><B><font face="Arial, Helvetica, sans-serif">Device Name</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">Model</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">Manufacturer</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">OS Version</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">EPL</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">EAL</font></B></td>
    				<td><B><font face="Arial, Helvetica, sans-serif">EXEMPT</font></B></td>
    			</tr>
    
    		<?php
    		$i=0;
    		while (($row=mysql_fetch_assoc($result))!==FALSE {
    
    			echo '<tr>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['label'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['Model'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['Manufacturer'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['serial'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['exe'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['eal'], '</font></td>
    			<td><font face="Arial, Helvetica, sans-serif">', $row['epl'], '</font></td>
    			</tr>';
    		}
    		?>
    		</table>
    	</body>
    </html>
    
  6.  

    SELECT d.label, d.serial, d.exe, d.eal, d.epl, dt.Model, m.Manufacturer
    FROM fac_Device AS d
    JOIN fac_DeviceTemplates AS dt ON d.TemplateID = dt.TemplateID
    JOIN fax_Manufacturer AS m ON dt.ManufacturerID = m.ManufacturerID
    WHERE d.label = 'server1'
    

     

    A few simple joins and you are good to go.

     

    ~awjudd

  7. You have ticks in your query when I'm guessing they should be quotes.

     

    // Current
    $select = "SELECT * FROM games WHERE game=`$get`";
    
    // Should be (if $get is a string)
    $select = "SELECT * FROM games WHERE game='$get'";
    
    // Should be (if $get is a int)
    $select = "SELECT * FROM games WHERE game=$get";

     

    Please note: you should either be validating the value in $_GET['game'], using an escape-string type function or database parameters. Otherwise there will be SQL injection.

     

    ~awjudd

  8. But that list isn't a list of entries with a unique category ID. 1 matches dog and horse, while 2 matches cat and bird. If anything, the query you are talking about should only return "bird".

     

    One way you could do it is but not 100% correct ...

     

    SELECT c.category, c.article_id, c2.name
    FROM ( SELECT category,MIN(article_id) AS article_id FROM categories GROUP BY category ) AS c
    JOIN categories AS c2 ON c.category = c2.category AND c.article_id = c2.article_id
    

     

    This will get you the MINIMUM article id for each category and then you can do your match on it.

     

    ~awjudd

  9. - '/meatspin/si' => 'Meatspin link found!!!', - just because the code says 'meatspin' doesn't mean it is a meat spin link

    - '/(is_null|isset|empty|intval|strval)/' => 'use type checks instead of type functions', - you are saying don't use things like isset / empty to validate that something was posted ... either you are forcing it so warnings happen or something ...

    '/\<\?([^=|^php])/' => 'short php tags should only be used to echo content', - short tags shouldn't be use (IMO) because the setting normally varies on each server and you can spend at least 20 minutes investigating an issue with that

    - '/(TRUE|FALSE|NULL)/' => 'booleans should be lower case', - NULL isn't a boolean

    - '/(\$[A-Z]+)/' => 'variable names should be lower case', - So someone can't use camel case? $userid instead of $userId?  That is horribly less readable

     

    IMO this is way too rigid to be of any use ...

     

    ~awjudd

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.